Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Min/Max costing

A question about the min/max index scan appeared on the Oracle Developer Community forum recently. The query supplied in the thread was a little odd – you might ask why anyone would run it as it stands – and I’ve modified it to make it even stranger to demonstrate a range of details.

I’ll start with a simple data set, not bothering to collect stats because that will be done automatically on create for my versions:

rem
rem     Script:         min_max_cost_bug.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jul 2020
rem     Purpose:        
rem
rem     Last tested 
rem             19.3.0.0
rem             12.2.0.1
rem

create table t1 
as 
select  * 
from    all_objects 
where   rownum <= 50000 -- > comment to avoid wordpress format issue
;

create index t1_i1 on t1(object_name);

Now a few simple queries – for which I’ll capture and display the in-memory execution plans a little further on:


set linesize 156
set pagesize 60
set trimspool on
set serveroutput off
alter session set statistics_level = all;

prompt  =====================
prompt  Baseline select max()
prompt  =====================

select max(object_name) from t1;
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last'));

prompt  ============================
prompt  select max() with dummy join
prompt  ============================

select max(object_name) from t1, dual where dummy is not null;
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last'));

prompt  =============================================
prompt  select max() with dummy join and index() hint
prompt  =============================================

select /*+ index(t1) */  max(object_name) from t1, dual where dummy is not null;
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last'));

prompt  ============================================
prompt  select max() with dummy join and inline view
prompt  ============================================

select  obj
from    (
        select  max(object_name)  obj
        from    t1
        ),
        dual 
where   dummy is not null
/

select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last alias'));

prompt  ====================================
prompt  select max() with existence subquery
prompt  ====================================

select max(object_name) from t1 where exists (select null from dual where dummy is not null);
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last alias'));

prompt  ============================================
prompt  select max() with failing existence subquery
prompt  ============================================

select max(object_name) from t1 where exists (select null from dual where dummy is null);
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last alias'));

With 50,000 rows and the appropriate index to allow Oracle to find the maximum value very quickly we expect the optimizer to invoke the “index full scan (min/max)” operation, visiting only the extreme leaf block of the index – and, indeed, we are not disappointed, that’s exactly what the baseline query shows us:

=====================
Baseline select max()
=====================
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |      1 |        |     3 (100)|      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE            |       |      1 |      1 |            |      1 |00:00:00.01 |       3 |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T1_I1 |      1 |      1 |     3   (0)|      1 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------------

However, when we introduce the (as yet unjustified) join to dual something very different happens – the optimizer forgets all about the min/max optimisation and does an index fast full scan of the t1_i1 index, passing all 50,000 rows up to the parent operation.


============================
select max() with dummy join
============================
-------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |      1 |        |    50 (100)|      1 |00:00:00.02 |     360 |
|   1 |  SORT AGGREGATE        |       |      1 |      1 |            |      1 |00:00:00.02 |     360 |
|   2 |   NESTED LOOPS         |       |      1 |  50000 |    50   (6)|  50000 |00:00:00.01 |     360 |
|*  3 |    TABLE ACCESS FULL   | DUAL  |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       3 |
|   4 |    INDEX FAST FULL SCAN| T1_I1 |      1 |  50000 |    48   (7)|  50000 |00:00:00.01 |     357 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("DUMMY" IS NOT NULL)

We could, of course, try hinting an index range (full) scan to see what happens – and the result is even more surprising: Oracle takes the hint, uses the min/max optimisation, and shows us that it didn’t take that path by default because it had “forgotten” how to cost it correctly.

Note the cost of 354 at operation 5 when the original min/max cost was 3, note also that the optimizer thinks we have to visit all 50,000 index entries even though, at run-time, Oracle correctly uses a path that visits only one index entry:


=============================================
select max() with dummy join and index() hint
=============================================
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |        |   356 (100)|      1 |00:00:00.01 |       6 |
|   1 |  SORT AGGREGATE              |       |      1 |      1 |            |      1 |00:00:00.01 |       6 |
|   2 |   NESTED LOOPS               |       |      1 |  50000 |   356   (2)|      1 |00:00:00.01 |       6 |
|*  3 |    TABLE ACCESS FULL         | DUAL  |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       3 |
|   4 |    FIRST ROW                 |       |      1 |  50000 |   354   (2)|      1 |00:00:00.01 |       3 |
|   5 |     INDEX FULL SCAN (MIN/MAX)| T1_I1 |      1 |  50000 |   354   (2)|      1 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("DUMMY" IS NOT NULL)

Of course we could recognise that the t1 access and the access to dual could be de-coupled – and hope that the optimizer doesn’t try to use complex view merging (maybe we should have included a /*+ no_merge */ hint) to fall back to a simple join. Fortunately the optimizer doesn’t try merging the two query blocks, so it optimises the max(object_name) query block correctly, giving us the benefit of the min/max optimisation. I’ve included the ‘alias’ format option in this call to dbms_xplan() so that we can see the two query blocks that are optimised separately.


============================================
select max() with dummy join and inline view
============================================

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |        |     5 (100)|      1 |00:00:00.01 |       6 |
|   1 |  NESTED LOOPS                |       |      1 |      1 |     5   (0)|      1 |00:00:00.01 |       6 |
|*  2 |   TABLE ACCESS FULL          | DUAL  |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       3 |
|   3 |   VIEW                       |       |      1 |      1 |     3   (0)|      1 |00:00:00.01 |       3 |
|   4 |    SORT AGGREGATE            |       |      1 |      1 |            |      1 |00:00:00.01 |       3 |
|   5 |     INDEX FULL SCAN (MIN/MAX)| T1_I1 |      1 |      1 |     3   (0)|      1 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / DUAL@SEL$1
   3 - SEL$2 / from$_subquery$_001@SEL$1
   4 - SEL$2
   5 - SEL$2 / T1@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DUMMY" IS NOT NULL)

There is a maxim (or guideline, or rule of thumb) that if the from clause of a query includes tables that don’t get referenced in the select list then those tables should (probably) appear in subqueries. Of course this guideline sometimes turns out to be a very bad idea, and sometimes it just means the optimizer unnests the subqueries and recreates the joins we started with, but let’s try the approach with this query. I’ve included the ‘alias’ option again so that you can see that this plan is optimised as two query blocks, allowing the max(object_name) query block to find the min/max strategy.


====================================
select max() with existence subquery
====================================
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |        |     5 (100)|      1 |00:00:00.01 |       6 |
|   1 |  SORT AGGREGATE              |       |      1 |      1 |            |      1 |00:00:00.01 |       6 |
|*  2 |   FILTER                     |       |      1 |        |            |      1 |00:00:00.01 |       6 |
|   3 |    FIRST ROW                 |       |      1 |      1 |     3   (0)|      1 |00:00:00.01 |       3 |
|   4 |     INDEX FULL SCAN (MIN/MAX)| T1_I1 |      1 |      1 |     3   (0)|      1 |00:00:00.01 |       3 |
|*  5 |    TABLE ACCESS FULL         | DUAL  |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       3 | 
-------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   4 - SEL$1 / T1@SEL$1
   5 - SEL$2 / DUAL@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( IS NOT NULL)
   5 - filter("DUMMY" IS NOT NULL)

There’s a very important detail in the execution plan above. At first sight it looks like the optimizer has a plan using a simple filter subquery operation – which means you might be fooled into reading it as “for each row returned by operation 3 call operation 5”. This is not the case.

Because the subquery is not a correlated subquery – it’s an example that I sometimes call a “fixed” or (slightly ambiguously) “constant” subquery – Oracle can execute it once and use the resulting rowsource to decide whether or not to call the main query. It’s a case where (if you didn’t realise the plan consisted of two separate query blocks) you would say that Oracle was calling the second child first.

To prove this point I’ve set up one last variation of the query – the “failed subquery” version – where my select from dual returns no rows. Check the numbers of Starts shown for each line of the plan:


============================================
select max() with failing existence subquery
============================================
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |        |     5 (100)|      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE              |       |      1 |      1 |            |      1 |00:00:00.01 |       3 |
|*  2 |   FILTER                     |       |      1 |        |            |      0 |00:00:00.01 |       3 |
|   3 |    FIRST ROW                 |       |      0 |      1 |     3   (0)|      0 |00:00:00.01 |       0 |
|   4 |     INDEX FULL SCAN (MIN/MAX)| T1_I1 |      0 |      1 |     3   (0)|      0 |00:00:00.01 |       0 |
|*  5 |    TABLE ACCESS FULL         | DUAL  |      1 |      1 |     2   (0)|      0 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   4 - SEL$1 / T1@SEL$1
   5 - SEL$2 / DUAL@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( IS NOT NULL)
   5 - filter("DUMMY" IS NULL)

The filter at operation 3 calls operation 5 – the query against dual – which runs once returning no rows. The min/max scan of t1_i1 at operation 4 doesn’t run. Operation 5 was called before operation 4 was considered.

Finally

This brings us back to the question – why would anyone run a strange query like this.

Perhaps the answer is that it’s just a demonstration of one part of a more complex query and what we’re trying to do is say: “if a certain record exists in a control table then include some information from table X”.

This note tells us that if there’s a possibility of a min/max optimisation to find the data then we should avoid using a join, instead we should use a “fixed subquery” to check the control table, and maybe we’ll also have to write the part of our query that collects (or isn’t required to collect) the interesting bit of data as an inline view.

 

Linux Containers (LXD) and Oracle 18cXE – installation

There’s a lot of articles about installing Oracle XE 18c on Docker. But Docker is not the only containerization technology on the market.

In this article, we are going to create Oracle XE 18c installation in Linux Containers (LXD)

A bit of history – LXC vs LXD

When we start our adventure with Linux Containers we might get confused by 2 different naming conventions – LXC and LXD. To clarify let’s talk about some history.

In the year 2000 FreeBSD developers introduced the jail command, which was used to isolate an application process from the main operating system space, but it was not very intuitive and functional.

Fortunately, it was not a waste of time, because 6 years later Google developers used the idea of isolation to introduce the new project – Control Groups (cgroups) – which was added to Linux kernel in version 2.6.24.

From here it was only one step to create Linux Containers (LXC) as a first containerization technology, which led to the birth of Docker in 2013.

Since Docker was far more intuitive in use than LXC but was used in a bit different philosophy (stateless containers), 2 years later LXD was born to give a new quality to Linux Containers

LXD isn’t a rewrite of LXC, in fact it’s building on top of LXC to provide a new, better user experience. Under the hood, LXD uses LXC through liblxc and its Go binding to create and manage the containers. It’s basically an alternative to LXC’s tools and distribution template system with the added features that come from being controllable over the network.

https://linuxcontainers.org/lxd/introduction/

https://blog.ora-600.pl/wp-content/uploads/2020/07/lxd-300x268.png 300w, https://blog.ora-600.pl/wp-content/uploads/2020/07/lxd-200x179.png 200w" sizes="(max-width: 410px) 100vw, 410px" />
https://linux.softpedia.com/

SNAP

In order to install LXD, we have to have SNAP package manager – this is how to install it on OEL/CENTOS 8:

First, we have add the newest EPEL repository:

# dnf -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm

Once we have it, we can install and configure SNAPD:

# dnf -y install snapd
# systemctl enable --now snapd.socket
# ln -s /var/lib/snapd/snap /snap

And install LXD

# setenforce 0
# snap install lxd

LXD configuration

Before configuring LXD it is a good idea to create a separate volume, which will be used as a default storage for new containers

[root@rick ~]# pvcreate /dev/sdb
  Physical volume "/dev/sdb" successfully created.
[root@rick ~]# vgcreate lxd-group /dev/sdb
  Volume group "lxd-group" successfully created
[root@rick ~]# lvcreate -ncontainers -l+100%FREE lxd-group
  Logical volume "containers" created.

Now we can configure LXD:

[root@rick ~]# lxd init
Would you like to use LXD clustering? (yes/no) [default=no]:
Do you want to configure a new storage pool? (yes/no) [default=yes]:
Name of the new storage pool [default=default]:
Name of the storage backend to use (btrfs, dir, lvm, ceph) [default=btrfs]:
Create a new BTRFS pool? (yes/no) [default=yes]:
Would you like to use an existing block device? (yes/no) [default=no]: yes
Path to the existing block device: /dev/lxd-group/containers
Would you like to connect to a MAAS server? (yes/no) [default=no]:
Would you like to create a new local network bridge? (yes/no) [default=yes]:
What should the new bridge be called? [default=lxdbr0]:
What IPv4 address should be used? (CIDR subnet notation, “auto” or “none”) [default=auto]:
What IPv6 address should be used? (CIDR subnet notation, “auto” or “none”) [default=auto]: none
Would you like LXD to be available over the network? (yes/no) [default=no]:
Would you like stale cached images to be updated automatically? (yes/no) [default=yes]
Would you like a YAML "lxd init" preseed to be printed? (yes/no) [default=no]:


Let’s create the first container!

[root@rick ~]# lxc launch images:oracle/7/amd64 mortyc-137

Done! We have a first container, named mortyc-137 based on OEL7! Let’s list our containers:

[root@rick ~]# lxc ls
+------------+---------+---------------------+------+-----------+-----------+
|    NAME    |  STATE  |        IPV4         | IPV6 |   TYPE    | SNAPSHOTS |
+------------+---------+---------------------+------+-----------+-----------+
| mortyc-137 | RUNNING | 10.94.183.27 (eth0) |      | CONTAINER | 0         |
+------------+---------+---------------------+------+-----------+-----------+

In order to install Oracle XE 18c, we need to prepare our container properly. By default, we have a NAT bridge attached to our container, which will allow access to the host network.

To login to your new container, just type:

# lxc exec morty-c137 bash

This is what we have to do:

  • Configure yum repository: yum install -y oraclelinux-release-el7.x86_64
  • Install Oracle RDBMS preinstall packet: yum install -y oracle-database-preinstall-18c.x86_64
  • Comment out memlock soft and memlock hard from file /etc/security/limits.d/oracle-database-preinstall-18c.conf
  • Install our XE 18c database: yum -y install https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-18c-1.0-1.x86_64.rpm
  • Our last step is of course standard XE configuration: /etc/init.d/oracle-xe-18c configure

That’s it! Let’s polish it </p />
</h2></div>

    	  	<div class=

Video : Resource Manager : Runaway Query Management

In today’s video we give a quick demonstration of using Resource Manager to cancel a long running SQL statement.

This video is based on a series of articles from Oracle 8i to the present day. There is an example here.

Although there are aspects of it throughout most of the Resource Manager articles on the website.

The horror show was brought to you by Jeff Smith. Notice his proximity to assorted torture devices he probably describes as “tools” when questioned by the authorities.

Cheers

Tim…


Video : Resource Manager : Runaway Query Management was first posted on July 13, 2020 at 8:03 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Two Years at Microsoft

This is commonly the time at any company I discuss how I’m about to move on…

Nope, not planning on going anywhere… I know, it’s odd for everyone involved, but we’ll all get used to it.

Where I’ve Been

I joined Microsoft two years ago, to take on the role that Patrick LeBlanc left so that he could accomplish all that we’ve observed with Adam Sexton on Guy in a Cube.  It was just a bit intimidating, as I’d never done analytics or AI before, nor had I really any experience in Azure. I hadn’t even thought about the big shoes I’d have to fill that Patrick had been wearing.  Lucky for me, I had a great team to work with.  I have a tendency to kind of figure things out in my own way and Dustin Ryan, Steve Pontello, (and later on, Hope Foley) made it surprisingly easy for me to kind of fill in the gaps where I was needed, including discovering the gap at Microsoft for Oracle.

Data and AI in EDU

Over a year ago, I was busy automating the modern data warehouse workloads, building out analytics/data platform solutions for customers and another 35% on anything Oracle that came up in the Education space, (High Ed and K-12).  I’d been approached by Israel Vega from the global team, (which was just developing at that point) and asked if I would join, but I really loved my current team and my boss, Denny, so I chose to pass up the opportunity to transfer.  Anyone in tech knows, when you find a place that you love, you hang on for dear life and Education is an awesome group to work with and my team was fantastic.

As the Oracle workload grew and I became known for being able to do this type of work, groups outside of Education came to my manager and asked to borrow me.  As we were able to handle the workload as we automated more in the cloud, the exception was made for me to work on a few customers, starting with ones that were “connected” to Education customers, (municipalities that had education dependencies, etc.)

The Shift

Almost six months went by and upon traveling to PASS Summit, speaking to attendees and peers, it became apparent that my focus was shifting dramatically.  I was up to 70% Oracle on Azure and more of my customers were outside of my Education team, (most were now global) than inside of it.  I was still communicating with the global team and had recommended my spouse and first mentor, Tim Gorman to the team when they asked for someone with similar skills to my own.  They interviewed him, loved him, (of course they did!) and hired him.  At that time, there was still a clear interest in bringing me onto the global Customer Success Unit, (CSU) team and as I came with my own customer base, I was an easy acquisition for them to justify.

My Second Team

I joined the CSU as an Subject Matter Expert, (SME) for Oracle on Azure Engineer the first day of 2020 and it’s been a whirlwind since.  I’d been focused and was very aware of what we needed to make Oracle on Azure successful.  I’d been taking on a consulting role vs. Cloud Solution Architect, (CSA) role and had been working with the small, exclusive group on the Oracle side at Microsoft for some time.  It was simple for me to ramp it up fast and started to design how we went about lifting and shifting Oracle to Azure, in all it’s forms.  Having Tim work with me on this was a quick shift, as we’ve been working together for years and he is my partner in life, so we understand each other’s work styles quite well.  The team we belong to quite large-  almost 40 individuals with skills covering data, AI, Analytics, networking, identity, Windows and Linux.  We’re about to be restructured to allow us to work more efficiently, but I’m thankful for those that I’ve got to meet and work with in the last six months in the CSU.

In the short time I’ve been here and with only a couple individuals to contribute to what’s been done, we’ve:

  • Identified how to properly size almost every Oracle workload for Azure.
  • What in Oracle is supported, what is certified and how to create an end-to-end solution in Azure for Oracle
  • Rewrote all the Oracle for Azure documentation, (still short of publishing, but oh, so close.)
  • Started recording internal training sessions for the Cloud Solution Architects, (CSAs) with architecture, technical tips and tricks and best practices to help them do so they aren’t so dependent on our small group.
  • Built a massive, internal Oracle for Azure channel on Teams that Microsoft folks come to when they have questions or need documentation, etc.
  • Built scripts and solutions in Github for anyone to use to make Oracle on Azure simpler.
  • Have posted to blogs, (both DBAKevlar, Microsoft Data Architecture Blog) done podcasts, web sessions and in-person presentations to let people know that Oracle on Azure is an easy and viable option for anyone.

As June and the 2020 fiscal year closes at Microsoft, along with my first two years, I’m really amazed the opportunities I’ve had to build out a role which wasn’t what I originally was hired for and for my managers, (three so far!) have willingly allowed me to build.  I’d like to thank Denny Ramsey, Christian Linacre and Vincent Staropoli for offering solid leadership while I have and continue to:

  • Do what I think needs to be done, say what needs to be said, even when they wish I’d step back.
  • Never, never, never give up.
  • Find a way around and roadblock or create a mob to help break through it in the end.
  • And through it all, hopefully use my powers for good.

I’m excited for what I’m about to take on in the coming months and can’t wait to see where it will take our customers and Microsoft.

So yes, you’re stuck with me, Microsoft, for as long as you will have me… </p />
</p></div>

    	  	<div class=

Massive Deletes

One of the recurrent questions on the Oracle Developer Commuity forum is:

What’s the best way to delete millions of rows from a table?

There are an enormous number of relevant details that you need to know before you can give the “right” answer to this question, e.g.

  • Which version of Oracle
  • Standard or Enterprise Edition
  • Is “millions” a tiny percentage of the table or a large percentage
  • Are there any referential integrity constraints in place
  • Does the system have to keep running while the deletion takes place
  • Is the table compressed
  • How many indexes are there – and can you drop some of them
  • How much space do you have to do this job
  • How much time do you have to do this job

One of the most important ones, of course, is “Which version of Oracle?” because it can make an enormous difference to the range of possible strategies. I’m writing this particular note because the question came up a little while ago where the user wanted to delete all the data from 2008 through to the end of 2018, keeping only the last 18 months of data.

That sounds like the volume of data to be deleted (11 years) is very much larger than the volume of data to be kept (1.5 years) – but we can’t be sure of that since businesses tend to grow over time so that last 18 months of data might actually be just as big as the previous 11 years.

As usually happens in response to this question there were suggestions to “create a new table selecting the data you want to keep”, “use dbms_parallel_execute to delete by rowid ranges in parallel”, and a relatively new one “convert to a partitioned table so that the data you want to keep is in its own partition and drop the other partition(s)”. 

I wrote a note a few years ago giving an example of converting a simple heap table to a partitioned table – while eliminating the data you don’t want to keep so there’s no need to waste resources copying redundant data, maintaining indexes (choosing between local and global) and doing the whole job online.  So, after learning that the OP was running 12.2 Enterprise Edition with the Partitioning option, I suggested that (s)he convert the table into a hash partitioned table with a single partition as this should (for purposes of optimisation) behave just like a simple heap table using the “including rows” clause to copy only the last 18 months of data.

I pointed out that their version of Oracle(EE + PO) gave them the 2nd best option – because I knew that in 19c you could simply do something like:

rem
rem     Script:         122_move.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jul 2020
rem     Purpose:        
rem
rem     Last tested 
rem             12.2.0.1
rem

create table t1
as
select  *
from    all_objects
where   rownum <= 50000
;

alter table t1 add constraint t1_pk primary key(object_id);

alter table t1 move
        including rows where owner != 'SYS'
        online
;

It wasn’t until a little later that a tiny nagging doubt crept into my mind that maybe this enhancement to the basic move command may have appeared at the same time as the modify partition enhancement – in other words in 12.2.0.1; so I ran the test above and found that it did actually seem to work. (And I haven’t yet found any bugs on MOS suggesting that it shouldn’t be used.)

Having discovered that the command was available I thought that I’d better check whether it was also documented, and found that it was in the 12.2 SQL Reference Manual (though not the 12.1 reference manual – for the obvious reason) under Alter Table. Page down to the “tram-tracks” for the Alter Table command and follow the link for the “move_table_clause”, and from there follow the link for “filter_condition”.

Note:

This option is not available on 12.1, and if you run the test Oracle will raise error “ORA-25184: column name expected” at the point where the word “rows” appears. This may look somewhat counter-intuitive, but in that version of Oracle a command like “alter table TabX move including ColY online”; is how you would rebuild an index organized table (IOT) with all columns up to ColY in the “IOT_TOP” segment.

Update [The following morning]

Once you’ve got the framework of a test in place it really doesn’t take very long to start running through “what if” cases or potential boundary conditions.  So this morning I added one very obvious test – what happens if you have referential integrity declared between two tables and try to move both of them including a subset of rows from each that ensures that the referential integrity is still in place:


rem
rem     Script:         122_move_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jul 2020
rem     Purpose:        
rem
rem     Last tested 
rem             19.3.0.0        Parent can't move
rem             12.2.0.1        Parent can't move
rem

create table parent
as
select  *
from    all_objects
where   rownum <= 50000
;

alter table parent add constraint par_pk primary key(object_id);


create table child
as
select  *
from    parent
order by
        dbms_random.value
;


alter table child add constraint chi_pk primary key(object_id);
alter table child add constraint chi_fk_par foreign key(object_id) references parent;

I’ve created the child table from the parent data, with random ordering. Now I’m going to delete all the child rows where owner = ‘PUBLIC’ using an online move, then I’ll try and do the same for the parent.


alter table child move
        including rows where owner != 'PUBLIC'
        online
;

-- Child move succeeds (of course)

alter table parent move
        including rows where owner != 'PUBLIC'
        online
;

--
-- Trying to do the matching move on the parent results in:
-- ORA-02266: unique/primary keys in table referenced by enabled foreign keys
--

So there’s a gap in the functionality that makes it less desirable than the simplest case suggests. The referential integrity constraint has to be disabled before the parent table can be restructured.

But something that merits a little further investigation is the option to set the foreign key to “disable validate” (which is sufficient to allow the parent move to take place) and then to set the constraint back to “enable”. When I tried this I had expected Oracle to do a lot of work to revalidate the constraint before enabling it, but I couldn’t find any indication that any such work had taken place.

Recursive WITH upgrade

There’s a notable change in the way the optimizer does cost and cardinality calculations for recursive subquery factoring that may make some of your execution plans change – with a massive impact on performance – as you upgrade to any version of Oracle from 12.2.0.1 onwards. The problem appeared in a question on the Oracle Developer Community forum a little while ago, with a demonstration script to model the issue.

I’ve copied the script – with a little editing – and reproduced the change in execution plan described by the OP. Here’s my copy of the script, with the insert statements that generate the data (all 1,580 of them) removed.

rem
rem     Script:         recursive_with_4.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jul 2020
rem     Purpose:        
rem
rem     Last tested 
rem             12.2.0.1
rem             12.1.0.2
rem
rem     Notes:
rem     https://community.oracle.com/thread/4338248
rem
rem     The upgrade to 12.2.0.1 made this query much slower (on 15,000 rows)
rem     Setting OFE to 12.1.0.1 is a first possible fix for the issue.
rem     The scale is too small to see much difference in this case
rem

drop table test_folder purge;

create table test_folder(
        fldr_key                number(16,0)            not null        enable,                 
        fldr_id                 varchar2(255 byte)      not null        enable,                 
        fldr_desc_tx            varchar2(255 byte),                     
        par_fldr_key            number(16,0),                   
        seus_key                number(16,0)            not null        enable,                 
        fldr_private_flg        varchar2(1 byte)        not null        enable,                 
        last_updt_dt            date                    not null        enable,                 
        last_upby_seus_key      number(16,0)            not null        enable,                 
        lock_seq_nbr            number(9,0) default 0   not null        enable,                 
        content_guid            raw(16),                
        constraint test_folder_pk primary key (fldr_key)                
)       
;              

-- list of insert statements

alter table test_folder add constraint test_folder_fk  
        foreign key (par_fldr_key) references test_folder(fldr_key)
;  
  
create or replace force editionable view test_folder_vw (fldr_key) as   
with rec_path(fldr_key)  as (
        select  tf.fldr_key  
        from    test_folder tf where tf.par_fldr_key is null  
        union all  
        select  tf.fldr_key  
        from    test_folder tf, rec_path  
        where   rec_path.fldr_key = tf.par_fldr_key
)  
select fldr_key  
from rec_path   
; 

begin
        dbms_stats.gather_table_stats(
                ownname     => null,
                tabname     => 'TEST_FOLDER',
                method_opt  => 'for all columns size 1'
        );
end;
/


select * from test_folder_vw where fldr_key = -41;  

I’ve run the test 3 times. First in 12.2.0.1 with no tweaking; then in 12.2.0.1 with the hint /*+ optimizer_features_enable(‘12.1.0.2’) */ and finally in a genuine 12.1.0.2 environment. In all three cases I enabled rowsource execution stats (‘alter session set statistics_level = all’) and pulled the plans from memory – with the following results

First, the base result from 12.1.0.2

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |             |      1 |        |      1 |00:00:00.03 |     604 |       |       |          |
|*  1 |  VIEW                                     |             |      1 |    801 |      1 |00:00:00.03 |     604 |       |       |          |
|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|             |      1 |        |   1580 |00:00:00.03 |     604 | 36864 | 36864 |  102K (0)|
|*  3 |    TABLE ACCESS FULL                      | TEST_FOLDER |      1 |    161 |    161 |00:00:00.01 |      68 |       |       |          |
|*  4 |    HASH JOIN                              |             |      8 |    640 |   1419 |00:00:00.02 |     536 |  1696K|  1696K| 1488K (0)|
|   5 |     RECURSIVE WITH PUMP                   |             |      8 |        |   1580 |00:00:00.01 |       0 |       |       |          |
|*  6 |     TABLE ACCESS FULL                     | TEST_FOLDER |      8 |   1419 |  11352 |00:00:00.01 |     536 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("FLDR_KEY"=(-41))
   3 - filter("TF"."PAR_FLDR_KEY" IS NULL)
   4 - access("REC_PATH"."FLDR_KEY"="TF"."PAR_FLDR_KEY")
   6 - filter("TF"."PAR_FLDR_KEY" IS NOT NULL)

Two points to note, in particular. First that the hash join has the recursive with pump as its first (build table) child and the table access full of test_folder as its second child (probe table); secondly that there is no value given for E-Rows for the recursive with pump.

Now the 12.2.0.1 plan:

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |             |      1 |        |      1 |00:00:00.01 |      47 |       |       |          |
|*  1 |  VIEW                                     |             |      1 |   2524K|      1 |00:00:00.01 |      47 |       |       |          |
|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|             |      1 |        |   1580 |00:00:00.01 |      47 | 36864 | 36864 |  102K (0)|
|*  3 |    TABLE ACCESS FULL                      | TEST_FOLDER |      1 |    161 |    161 |00:00:00.01 |      24 |       |       |          |
|*  4 |    HASH JOIN                              |             |      8 |   2524K|   1419 |00:00:00.01 |      23 |  1743K|  1743K| 1632K (0)|
|   5 |     BUFFER SORT (REUSE)                   |             |      8 |        |  11352 |00:00:00.01 |      23 | 73728 | 73728 |          |
|*  6 |      TABLE ACCESS FULL                    | TEST_FOLDER |      1 |   1419 |   1419 |00:00:00.01 |      23 |       |       |          |
|   7 |     RECURSIVE WITH PUMP                   |             |      8 |        |   1580 |00:00:00.01 |       0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("FLDR_KEY"=(-41)
   3 - filter("TF"."PAR_FLDR_KEY" IS NULL)
   4 - access("REC_PATH"."FLDR_KEY"="TF"."PAR_FLDR_KEY")
   6 - filter("TF"."PAR_FLDR_KEY" IS NOT NULL)

There are three changes to notice in this plan – which (for the OP) was much slower than the 12.1.0.2 plan. First, the order of the hash join has changed, the recursive with pump is now the second child (probe table) in the join (and again shows no value for E-Rows); secondly that Oracle has introduced an extra operation – the buffer sort (reuse) – populated by the table access full – as the build table; thirdly (presumably the point of buffer sort (reuse) operation) the number of buffer visits has dropped from a few hundred to a couple of dozen.

Finally let’s check what happens if we set the OFE (optimizer_features_enable) to 12.1.0.2 while running 12.2.0.1

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |             |      1 |        |      1 |00:00:00.01 |      47 |       |       |          |
|*  1 |  VIEW                                     |             |      1 |    801 |      1 |00:00:00.01 |      47 |       |       |          |
|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|             |      1 |        |   1580 |00:00:00.01 |      47 | 36864 | 36864 |  102K (0)|
|*  3 |    TABLE ACCESS FULL                      | TEST_FOLDER |      1 |    161 |    161 |00:00:00.01 |      24 |       |       |          |
|*  4 |    HASH JOIN                              |             |      8 |    640 |   1419 |00:00:00.01 |      23 |  1797K|  1797K| 1573K (0)|
|   5 |     RECURSIVE WITH PUMP                   |             |      8 |        |   1580 |00:00:00.01 |       0 |       |       |          |
|   6 |     BUFFER SORT (REUSE)                   |             |      8 |        |  11352 |00:00:00.01 |      23 | 73728 | 73728 |          |
|*  7 |      TABLE ACCESS FULL                    | TEST_FOLDER |      1 |   1419 |   1419 |00:00:00.01 |      23 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("FLDR_KEY"=(-41))
   3 - filter("TF"."PAR_FLDR_KEY" IS NULL)
   4 - access("REC_PATH"."FLDR_KEY"="TF"."PAR_FLDR_KEY")
   7 - filter("TF"."PAR_FLDR_KEY" IS NOT NULL)

In these conditions the recursive with pump has gone back to being the build table (first child); but it’s worth noting that the 12.2 buffer sort (reuse) is still in place – saving us a few hundred buffer gets (and, for a bigger table, a number of disc reads possibly). Downgrading the optimizer_features_enable has given us the plan we needed, but this we’ve got an example that shows that hacking the parameter isn’t a guarantee that we will get exactly the path we used to get in the older version.

The story so far.

It seems that we can address the performance problem that the OP had by setting the optimzer_feature_enable to the older version – possibly through a hint embedded in the SQL, perhaps through an SQL Baseline or SQL Patch. Maybe we’ll have to have a logon trigger that sets the parameter for particular users or, worst case scenario, maybe we’ll have to set the parameter at the system level. Given how undesirable the last option could be it would be nice to know exactly what is causing the change in plan.

As a basic clue – if the order of tables in a hash join reverses itself this usually means that the byte (not row) estimates have changed. The optimizer will use the table with the lower byte count as the build table in a hash join. So the recursive with pump – whose row and byte estimates don’t appear – must have produced larger numbers in 12.2.0.1.

A change in the 12.2 plan that I haven’t yet mentioned is the E-rows for the hash join; it’s gone up from 640 (12.1.0.2) to 2.5 million! So let’s repeat the tests with the CBO (10053) trace file enabled and see if we can find a number like 2524K appearing as a join estimate in the trace file. Having created the two trace files (in 12.2.0.1, one with the OFE set backwards) I executed the following grep command against the trace files:

grep -n "^Join Card - Rounded" orcl12c_ora_5524_ofe.trc
grep -n "^Join Card - Rounded" orcl12c_ora_5524_base.trc

I’d set the tracefile_identifier to ‘ofe’ and ‘base’ respectively for the 12.1.0.2 and 12.2.0.1 tests, and here are the results:

grep -n "^Join Card - Rounded" orcl12c_ora_5524_ofe.trc
1166:Join Card - Rounded: 640 Computed: 639.941176

grep -n "^Join Card - Rounded" orcl12c_ora_5524_base.trc
1195:Join Card - Rounded: 640 Computed: 639.941176
1391:Join Card - Rounded: 2544 Computed: 2543.865546
1576:Join Card - Rounded: 10112 Computed: 10111.865546
1737:Join Card - Rounded: 40193 Computed: 40193.075630
1898:Join Card - Rounded: 159759 Computed: 159758.731092
2059:Join Card - Rounded: 635008 Computed: 635008.462185
2220:Join Card - Rounded: 2524023 Computed: 2524023.394958
2269:Join Card - Rounded: 2524023 Computed: 2524023.394958

That’s an interesting clue. Something seems to be calculating a larger and larger value in the 12.2.0.1 trace, starting with the hash join cardinality that appeared in 12.1.0.2 had, growing by a factor of nearly 4 each time, and ending with the hash join cardinality we saw in the 12.2.0.1 plan.

Taking a closer look at the content of the 12.2.0.1 trace file it turned out that every stage in that escalation was Oracle recalculating the cost and cardinality of joining test_folder (the table) and rec_path (the “with” subquery) using the figures from the previous join calculation as the new base figures for rec_path. In effect the optimizer was calculating the cost of following the recursive subquery down to its 7th level of recursion.

Side note: in agreement with my comment about the smaller (in bytes) rowsource being used as the build table, the initial join order started as (test_folder, rec_path) in the first two iterations, but switched to (rec_path, test_folder) from the 3rd iteration onwards.

So we’ve identified the mechanics that cause the change in plan; the question now is: why 7 iterations to the final cost? (Briefly I did a quick check to see how many circles of hell there were in Dante’s Inferno – but it’s 9 (or 10 depending how you count). A quick check of v$parameter (and the x$ tables for the hidden parameters) revealed the following:

Name                                     Value
------------------------------------ ---------
_recursive_with_branch_iterations            7

Setting this parameter to 1 in the session, or adding the hint /*+ opt_param(‘_recursive_with_branch_iterations’ 1) */ to the query resulted in the 12.1.0.2 plan appearing in 12.2.0.1 – and this is a much less intrusive way of getting the plan we’re interested in than setting the entire OFE back to 12.1.0.2. One might even set the parameter in the spfile (after receiving approval from Oracle Corp., of course) given how precisely targetted it is (and know that it doesn’t switch off that nice little buffering trick.)

Summary

From 12.2 onwards the optimizer does recursive recosting of recursive “with” subqueries. This means the cost and cardinality estimates of a plan may change and the impact may cause a significant change in performance – it certainly did for the OP.

The change seems to be driven by the hidden parameter _recursive_with_branch_iterations, which was introduced in 12.2.0.0 with a default value of 7. Setting this parameter to 1 reverts the optimizer to the pre-12.2 behaviour. If you run into a problem of recursive “with” subqueries changing plans and causing performance problems on an upgrade from pre-12.2 to a newer version of Oracle then it would be worth investigating this parameter as the least intrusive way of reverting back to the original plans.

Update (July 2020)

I’ve just been prompted to check MOS for any references to the hidden parameter – and discovered a note that was published in September 2018, updated ub Feb 2019.  It’s amazing how easy it can be to find an answer on MOS when you already know what the answer is ;) Document id 2443466.1 Oracle 12.2.0.1 CBO calculating high cost/CPU for queries with recursive sub-query (Doc ID 2443466.1)

This gives two workarounds to the problem of a change in cost in 12.2 – set the optimizer_features_enable to 12.1.0.2, or set the hidden parameter to 1. It references two bugs (one a duplicate of the other, both apparently unpublished):

  • Bug 23515289 : PERFORMANCE REGRESSION OBSERVED WITH RECURSIVE WITH SERIAL PLAN
  • Bug 24566985 : UPG: QUERY PERFORMANCE ON ALL_TSTZ_TABLES 160 TIMES SLOWER THAN 11.2.0.4

and the Permanent Fix for the problem is to install the patch for Bug 24566985 on 12.2.0.1

 

Uniquely random or Randomly unique

When sequences won’t do

In an Oracle database if someone comes to you and says “I need to generate unique numbers” then anyone with any experience of Oracle will more likely than not suggest a sequence. And that is good advice because a sequence is incredibly fast, scales well with multiple users, is very easy to code and is guaranteed unique. It is the perfect tool for generating surrogate keys, that is, meaningless numbers for primary keys in particular, which of course by the very definition of primary key, must be unique


SQL> create table t ( pk number);

Table created.

SQL> create sequence seq cache 1000;

Sequence created.

SQL>
SQL> set timing on
SQL> begin
  2    for i in 1 .. 100000 loop
  3      insert into t values (seq.nextval);
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.81

But sometimes those same unique identifiers that we want to generate, actually identifies a core piece of information that we reveal to the customers of our systems. The simplicity of a sequence is also a drawback in this instance.


SQL> select pk from t where rownum <= 20;

    PK
------
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20

because if I see a set of roughly contiguous numbers, and I know that they are, for instance, customer identifiers, then that is potentially a area of concern in terms of security. A a potential hacker may find an avenue to probe for customer identifiers simply by working their way through the contiguous pattern.

GUID to the rescue?

An alternative to sequences for these scenarios is to use a globally unique identifier. The database has a facility to do this as well via the SYS_GUID function It requires a touch more code but is very similar to the sequence solution. Commonly if you are using tools like QuickSQL then we convert the GUID to a number and it looks suitably random



SQL> set timing on
SQL> begin
  2    for i in 1 .. 100000 loop
  3      insert into t values (to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'));
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.99

SQL> select pk from t where rownum <= 20;

                                       PK
-----------------------------------------
  105854066610474458368090556750481295785
  186822031504444284720892382281585799957
  132888271924145856133313587357433340980
  276167378372574928342970152593385328900
   74086652057562125835746396515377682140
  286319886043592938247346128711951783544
  336167790885619983030212750918503752250
  288589581961303614692651099852065933187
  324920391859101420030979893594250781674
  126732679724678528065575262532843100163
  113307513485209808186387016050557945591
  200787479897466765534406064765323360878
  139368685100390784122367914412713398477
  238803160953268950614524266576466039309
  241991797837563579395004471847660812814
  120454289487617651156843055918296309789
  105272018464122283803281386350139492346
  198909665803908780010454156775156795329
  249866914728623919764347522241235284337
  339746714983781506209715409558673179734

20 rows selected.

Similarly, if we consider just the raw data type it also in this demo appears to be random:



SQL> drop table t purge;

Table dropped.

SQL> create table t ( pk raw(16));

Table created.

SQL> set timing on
SQL> begin
  2    for i in 1 .. 100000 loop
  3      insert into t values (sys_guid());
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.83
SQL> set timing off
SQL>
SQL> select pk from t where rownum <= 20;

PK
--------------------------------
CFF50A624B4F456A8E429D12CA55C67B
4E6B43C7B661401580A78F39DDDBA100
DF7AC2A6DB4F47A2AF3B560DCE4BBA32
B276B27FD6F640DB9A5669D21B86D374
2D2079BFEA494A7C90374DB6955A1263
2E5F210976C1423899FF736108842D72
A6E03758A3B2436D9941C7CAB87406D4
DBC3033CB54544D19CC1CD35FF8B4691
8C4542D16FB342878C2A8B230C21EE1E
1A2BD7CCD5CE43CDB7AAF2DC4615889B
274C873A645748C9B886F20427AD0B94
5E509539B2D4449A919459D6507078E3
8E8F2E18A34544CDBA2D2703CBCADD47
DA2244E2CFF443E382E6CE86C09F8615
F52369F769A147CE918E9E98426A8493
58F5D7B3F2094A35BA7BEAC6DCD93B39
BA330C0BB236430CB3228EF3C96102C5
F003DE9F90714016923DC83D2977F593
5F3E03A0AFF04F88857778C102F6D39D
BC0B24E1BFA0433D94AED3A8B489A9D6

20 rows selected.

But all is not as it seems because the generation of the value for SYS_GUID is platform dependent and when I run the same demo on Linux then suddenly our unique identifier suffers the same issue as the sequence namely it could potentially be predicted by a malicious party. It is almost hard to even distinguish that these values are unique, but I’ve highlighted the critical nybble.



SQL> set timing on
SQL> begin
  2    for i in 1 .. 100000 loop
  3      insert into t values (sys_guid());
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.22
SQL> set timing off
SQL>
SQL> select pk from t where rownum <= 20;

PK
--------------------------------
AA1252410FA30F15E0539314000A0C74
AA1252410FA40F15E0539314000A0C74
AA1252410FA50F15E0539314000A0C74
AA1252410FA60F15E0539314000A0C74
AA1252410FA70F15E0539314000A0C74
AA1252410FA80F15E0539314000A0C74
AA1252410FA90F15E0539314000A0C74
AA1252410FAA0F15E0539314000A0C74
AA1252410FAB0F15E0539314000A0C74
AA1252410FAC0F15E0539314000A0C74
AA1252410FAD0F15E0539314000A0C74
AA1252410FAE0F15E0539314000A0C74
AA1252410FAF0F15E0539314000A0C74
AA1252410FB00F15E0539314000A0C74
AA1252410FB10F15E0539314000A0C74
AA1252410FB20F15E0539314000A0C74
AA1252410FB30F15E0539314000A0C74
AA1252410FB40F15E0539314000A0C74
AA1252410FB50F15E0539314000A0C74
AA1252410FB60F15E0539314000A0C74

DBMS_RANDOM to the rescue?

Maybe a solution is to use the random number generation facilities inside the database. However the very definition of “random” runs counter to that of uniqueness. For a random number generator to be truly random, it could easily generate the same number 20 times in a row and still be valid in terms of randomness.

The lack of uniqueness when using a random number generator randomness this is easy to prove by doing a drop in replacement of our GUID code with DBMS_RANDOM. The demo runs to completion but we can easily detect that some duplicates occurred.



SQL> drop table t purge;

Table dropped.

SQL> create table t ( pk number);

Table created.

SQL> set timing on
SQL> begin
  2    for i in 1 .. 100000 loop
  3      insert into t values (abs(dbms_random.random));
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.14
SQL> set timing off
SQL>
SQL> select pk from t where rownum <= 20;

                                       PK
-----------------------------------------
                                702163599
                                628903810
                               1723610093
                                 53889653
                                552375825
                                715776656
                               1023547593
                               1180087997
                               1276294049
                               2118855978
                               1482501921
                               1298798620
                                  2674977
                                 45293496
                               2111077922
                               1749692232
                               1800848612
                                150316819
                                753335341
                               1252549540

20 rows selected.

SQL> select pk from t
  2  group by pk
  3  having count(*) > 1;

                                       PK
-----------------------------------------
                                918416655
                               2871298471

2 rows selected.

So to generate a identifier that is both random and therefore hard to guess but unique as well, we need to combine some of the techniques above. Only a sequence or a GUID is a guarantee of uniqueness so an identifier must at a minimum contain a usage of one of those.

Here is a potential solution. I will grab the seconds and fractions of a second from the current timestamp, and also I append to that a fixed width sequence to guarantee the uniqueness. I am choosing fixed width because if not, if there is a “bleed” of values between the timestamp values and the sequence values, the potential for duplicates would be increased. For example, a timestamp/sequence pair of “123-87” could clash with “12-387” if they were not both fixed width. And just to add a little more unpredictability I will suffix the value with a call to DBMS_RANDOM. Duplicate random numbers are not an issue, because the sequence element guarantees uniqueness.



SQL> drop table t purge;

Table dropped.

SQL> create table t ( pk number);

Table created.

SQL> drop sequence seq;

Sequence dropped.

SQL> create sequence seq start with 1000000 maxvalue 9999999 cycle;

Sequence created.

SQL> set timing on
SQL> begin
  2    for i in 1 .. 100000 loop
  3      insert into t values (
  4            to_number(trunc(dbms_random.value(1000,9999))||
  5                      to_char(systimestamp,'FFSS')||
  6                      seq.nextval));
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.87
SQL> set timing off
SQL> select pk from t where rownum <= 20;

                                         PK
-------------------------------------------
                        3607951782351001708
                        9784951885351001709
                        8223951981351001710
                        2862952057351001711
                        9015952125351001712
                        9447952198351001713
                        7311952264351001714
                        6102952328351001715
                        2204952392351001716
                        6937952455351001717
                        1260952519351001718
                        9725952584351001719
                        2274952648351001720
                        1274953133351001721
                        7580953210351001722
                        4164953278351001723
                        3659953344351001724
                        1928953410351001725
                        8497953476351001726
                        6248953542351001727

20 rows selected.

Running this demo you can still see that there is a slight semblance of a pattern due in the values due to the sequence but I would contend that this is still an impossible to guess identifier. And similarly I would contend that this identifier is “always” unique because in order to encounter a violation we would need to generate the same sequence number as it cycles around, along with the same random value from our random number generator, and chance upon both of those repeated values at the same fraction of a second within a one minute.

Too much information

Of course I started this post discussing the need for an identifier that reveals, for instance, a customer account. This would suggest that this identifier will be potentially provided to users directly, for example, when they register with our application they may be told “Congratulations, your customer ID is 123456” etc . Whilst we have a solution for our random and unique identifier requirement, that of course is now a very large number for them to remember and/or note down. Ideally we want to give them something smaller and simpler to remember. A solution is to pass them the value as reduced in size by converting the number to a larger base than decimal . Here is a simple example where we convert the identifier to base 34.



SQL> create or replace
  2  function base34(p_num number) return varchar2 is
  3    l_dig varchar2(34) := 'AB0CD1EF2GH3JK4LM5NP6QR7ST8UV9WXYZ';
  4    l_num number := p_num;
  5    l_str varchar2(38);
  6  begin
  7    loop
  8      l_str := substr(l_dig,mod(l_num,34)+1,1) || l_str ;
  9      l_num := trunc(l_num/34);
 10      exit when l_num = 0;
 11    end loop;
 12    return l_str;
 13  end;
 14  /

Function created.

SQL>
SQL> select base34(pk) from t where rownum <= 20;

BASE34(PK)
------------------------------------------------------------------------
CNZT85YD4ZGSD
BJ996YX6FXSVB
0DFQMNTKX9XFY
BWRWL8RMD7SEX
BL96GSRMUBM02
B3V5H8WNRZ5EK
C1F3LF9KFZNDW
7LASBJFMWTLQ
0AVVDLXUBPJYM
B9FMN324WK11C
0459EMZWT883J
0K9PPRBPNB3T1
05J27N0G9AB5R
BDZAVV0FLSGD5
068AZPEECMDE0
BMFSBC4X80STK
CRWA0G2BBAPVV
0R0CTVZBD72H1
B1TE2B2ZJWW36
CN8M8ATS83U11

20 rows selected.

SQL>
SQL> create or replace
  2  function dec34(p_str varchar2) return number is
  3    l_dig varchar2(34) := 'AB0CD1EF2GH3JK4LM5NP6QR7ST8UV9WXYZ';
  4    l_num number := 0;
  5  begin
  6    for i in 1 .. length(p_str) loop
  7      l_num := l_num * 34 + instr(l_dig,upper(substr(p_str,i,1)))-1;
  8    end loop;
  9    return l_num;
 10  end;
 11  /

Function created.

SQL> select to_char(pk) from t where rownum = 1
  2  union all
  3  select base34(pk) from t where rownum = 1
  4  union all
  5  select to_char(dec34(base34(pk))) from t where rownum = 1;

TO_CHAR(PK)
-----------------------------------------------------------------
3607951782351001708
B5KS4X1SQVMMV
3607951782351001708

You may be pondering “I have never seen base 34 as a commonly used base” and you would be correct Smile. I have chosen 34 because it is the letters of the alphabet and the decimal digits but I have removed the letter “I” and letter “O” because when provided to customers, they could easily be misinterpreted as the number 1 or 0. Using my own customised base conversion adds a little more obfuscation, but mainly lets me provide a better service to customers (and probably my call centre employees) when they have to ask for this identifier.

Summary

If you need to generate identifiers that are both unique and random always make sure you at least include either a sequence or a GUID, and then augment or manipulate that value to provide the randomness you desire.

Execution Plans

This is an example from the Oracle Developer Community of using the output of SQL Monitor to detect a problem with object statistics that resulted in an extremely poor choice of execution plan.

A short time after posting the original statement of the problem the OP identified where he thought the problem was and the general principle of why he thought he had a problem – so I didn’t have to read the entire execution plan to work out a strategy that would be (at least) a step in the right direction of solving the performance problem.

This note, then, is just a summary of the five minute that I spent confirming the OP’s hypothesis and explaining how to work around the problem he had identified. It does, however, give a little lead-in to the comments I made to the OP in order to give a more rounded picture of what his execution plan wass telling us.

So here’s the top half of the plan (which is the first subquery of a “concatenation”) with the first few predicates:

===============================================================================================================================================================================================================================  
| Id |                 Operation                  |            Name            |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  |  Cell   |  Mem  | Activity |             Activity Detail             |  
|    |                                            |                            | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes | Offload | (Max) |   (%)    |               (# samples)               |  
===============================================================================================================================================================================================================================  
|  0 | SELECT STATEMENT                           |                            |         |      |     12086 |     +5 |     1 |     864K |      |       |         |       |     0.01 | Cpu (1)                                 |  
|  1 |   CONCATENATION                            |                            |         |      |     12086 |     +5 |     1 |     864K |      |       |         |       |          |                                         |  
|  2 |    FILTER                                  |                            |         |      |     12191 |     +4 |     1 |     864K |      |       |         |       |     0.03 | Cpu (4)                                 |  
|  3 |     FILTER                                 |                            |         |      |     12191 |     +4 |     1 |      26M |      |       |         |       |     0.01 | Cpu (1)                                 |  
|  4 |      NESTED LOOPS                          |                            |     241 | 251K |     12191 |     +4 |     1 |      26M |      |       |         |       |     0.02 | Cpu (3)                                 |  
|  5 |       NESTED LOOPS                         |                            |     241 | 251K |     12191 |     +4 |     1 |      26M |      |       |         |       |     0.07 | Cpu (8)                                 |  
|  6 |        NESTED LOOPS                        |                            |     241 | 251K |     12232 |     +4 |     1 |      26M |      |       |         |       |     0.05 | Cpu (6)                                 |  
|  7 |         NESTED LOOPS                       |                            |    5407 | 233K |     12242 |     +4 |     1 |      86M |      |       |         |       |          |                                         |  
|  8 |          MERGE JOIN CARTESIAN              |                            |       1 |   35 |     12242 |     +4 |     1 |     1000 |      |       |         |       |          |                                         |  
|  9 |           TABLE ACCESS BY INDEX ROWID      | REF1                       |       1 |    3 |         1 |     +4 |     1 |        1 |      |       |         |       |          |                                         |  
| 10 |            INDEX RANGE SCAN                | REF1_PK                    |       1 |    2 |     12242 |     +4 |     1 |        1 |      |       |         |       |          |                                         |  
| 11 |           BUFFER SORT                      |                            |      84 |   32 |     12242 |     +4 |     1 |     1000 |      |       |         |  104K |          |                                         |  
| 12 |            TABLE ACCESS BY INDEX ROWID     | STAGE                      |      84 |   32 |         1 |     +4 |     1 |     1000 |      |       |         |       |          |                                         |  
| 13 |             INDEX RANGE SCAN               | STAGE_IDX1                 |      84 |    4 |         1 |     +4 |     1 |     1000 |      |       |         |       |          |                                         |  
| 14 |          PARTITION RANGE ITERATOR          |                            |    8292 | 232K |     12232 |     +4 |  1000 |      86M |      |       |         |       |          |                                         |  
| 15 |           TABLE ACCESS STORAGE FULL        | TAB1                       |    8292 | 232K |     12245 |     +1 |  1000 |      86M | 103M | 521GB |   1.96% |    7M |    51.81 | gc buffer busy acquire (1)              |  
|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | latch: cache buffers chains (1)         |  
|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | Cpu (1196)                              |  
|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | gcs drm freeze in enter server mode (2) |  
|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | reliable message (5)                    |  
|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | cell single block physical read (2827)  |  
|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | cell smart table scan (1977)            |  
|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | read by other session (304)             |  
| 16 |         PARTITION RANGE ITERATOR           |                            |       1 |   12 |     12191 |     +4 |   86M |      26M |      |       |         |       |     0.42 | Cpu (51)                                |  
| 17 |          TABLE ACCESS BY LOCAL INDEX ROWID | TAB2                       |       1 |   12 |     12191 |     +4 |   86M |      26M |   4M |  28GB |         |       |    32.14 | gc cr grant 2-way (20)                  |  
|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | gc cr request (2)                       |  
|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | gc remaster (6)                         |  
|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | Cpu (319)                               |  
|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | gcs drm freeze in enter server mode (4) |  
|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | latch: gc element (2)                   |  
|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | cell single block physical read (3563)  |  
| 18 |           INDEX RANGE SCAN                 | TAB2_IX1                   |     166 |    3 |     12210 |     +2 |   86M |      26M |   1M |  11GB |         |       |    15.17 | Cpu (292)                               |  
|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | cell single block physical read (1557)  |  
| 19 |        INDEX UNIQUE SCAN                   | MTD_PK                     |       1 |    1 |     12242 |     +4 |   26M |      26M |  292 |   2MB |         |       |     0.17 | Cpu (20)                                |  
|    |                                            |                            |         |      |           |        |       |          |      |       |         |       |          | cell single block physical read (1)     |  
| 20 |       TABLE ACCESS BY INDEX ROWID          | REF2                       |       1 |    2 |     12191 |     +4 |   26M |      26M |    7 | 57344 |         |       |     0.11 | Cpu (13)                                |  
| 21 |      TABLE ACCESS BY INDEX ROWID           | CONTROLTAB                 |       1 |    1 |         1 |     +4 |     1 |        1 |      |       |         |       |          |                                         |  
| 22 |       INDEX UNIQUE SCAN                    | CONTROLTAB_PK              |       1 |      |         1 |     +4 |     1 |        1 |      |       |         |       |          |                                         |  
| 23 |     MINUS                                  |                            |         |      |       102 |     +4 |    25 |        3 |      |       |         |       |          |                                         |  
| 24 |      TABLE ACCESS BY INDEX ROWID           | CUST_ORG_PK                |       1 |    3 |       942 |     +4 |    25 |       10 |      |       |         |       |          |                                         |  
| 25 |       INDEX UNIQUE SCAN                    | MC_PK                      |       1 |    2 |       942 |     +4 |    25 |       25 |      |       |         |       |          |                                         |  
| 26 |      SORT UNIQUE NOSORT                    |                            |       1 |    4 |         8 |     +4 |    25 |        9 |      |       |         |       |          |                                         |  
| 27 |       TABLE ACCESS BY INDEX ROWID          | REF1                       |       1 |    3 |         8 |     +4 |    25 |        9 |      |       |         |       |          |                                         |  
| 28 |        INDEX RANGE SCAN                    | REF1_PK                    |       1 |    2 |         8 |     +4 |    25 |        9 |      |       |         |       |          |                                         |  


Predicate Information (identified by operation id):  
---------------------------------------------------  
   2 - filter( EXISTS ( (SELECT /*+ INDEX_RS_ASC ("CUST_ORG_PK" "MC_PK") */ "CUST_ID" FROM "USER1"."CUST_ORG_PK"  "CUST_ORG_PK" 
               WHERE "CUST_ID"=:B1 AND "CUST_ORG_PK"."INDICATR"='Y') MINUS (SELECT /*+ INDEX_RS_ASC ("REF1" "REF1_PK") */ 
               TO_NUMBER("VAL") FROM "USER1"."REF1" "REF1" WHERE "PUSER"='ZZZ' AND "EDATE" .ge. TRUNC(SYSDATE@!) AND TO_NUMBER("VAL")=:B2  
               AND "SDATE" .le. TRUNC(SYSDATE@!))))  
   3 - filter( EXISTS (SELECT /*+ INDEX_RS_ASC ("CONTROLTAB" "CONTROLTAB_PK") */ 0 FROM  "USER2"."CONTROLTAB" "CONTROLTAB" WHERE
              "CONTROLTAB"."CNTRLID"=9999 AND  NVL("CONTROLTAB"."STATUS",'F')='S'))  
   9 - filter("REF1"."EDATE" .ge. TRUNC(SYSDATE@!))  
  10 - access("REF1"."PUSER"='XXX' AND  "REF1"."NAME"='CODE' AND "REF1"."SDATE" .le. TRUNC(SYSDATE@!))  
  13 - access("STAGE"."NAME"='XXX' AND "STAGE"."STAT"='I')  

Note: various inequality symbols changed to .le. / .ge. to avoid WordPress format issue.

The first thing to note is that the “Time (active)” shown at the top line is about 12,000 seconds – so it’s a long running query. Your next observation – before you look at the shape of the plan – might be to note that operations 15, 17 and 18 between them record thousands of seconds of time, mostly I/O time but including 1,200 seconds of CPU time. This might draw your eye to the part of the plan that tells you what you are doing at these operations and why you are doing it.

Looking at the detail – operation 15 is a full tablescan that is the child of a partition range iterator (operation 14), and that iterator is the second child of a nested loop join (operation 7). Why is the optimizer so out of balance that it thinks a table scan of (possibly) multiple partitions of a partitioned table is a good candidate for the second child of a nested loop?! The answer comes from the first child – the  Merge Join Cartesian at operation 8 has been given a cardinality estimate of just one row. When the cardinality estimate is one for the first table in a join then it doesn’t matter whether Oracle uses a nested loop join or a hash join, whatever happens next is only supposed to happen once.

Unfortunately when we compare “Rows (Estim)” and “Rows (Actual)” for the operation we see that the Merge Join Cartesian produced 1,000 rows – so the partition tablescan was going to happen 1,000 times (which we can confirm from the “Execs” column of operation 14). As a first step, then, we need to ensure that the optimizer gets a better estimate of the number of rows produced by the Cartesian join. So lets look at its child operations.

  • Operation 9 (Table Access of REF1) is predicted to return one row – with “Rows (actual)” = 1.
  • Operation 11 (Buffer Sort of data from STAGE1) is predicted to return 84 rows – with “Rows (actual)” = 1,000

Since the number of rows produced by a Cartesian join should be the product of the number of rows of the two inputs this tells us that the optimizer’s estimate of the number of rows from REF1 has been rounded up to 1 from a very small fraction (less than 1/84). If we can correct this then we may get Oracle to change the awful nested loop to an acceptable hash join. Wven if we can’t fix this mis-estimate we may be able to do something that improves the estimate for STAGE1 to something sufficienlty large that it will trigger the switch to a hash join. So let’s look at the predicates for these two tables.

REF1 predicates

   9 - filter("REF1"."EDATE">=TRUNC(SYSDATE@!))  
  10 - access("REF1"."PUSER"='XXX' AND  "REF1"."NAME"='CODE' AND "REF1"."SDATE"<=TRUNC(SYSDATE@!))  

The index range scan is based on an access predicate (with no filter predicate), so it looks like there’s a nearly perfect three-column index on REF1, but the optimizer can’t use the number of distinct keys in the index to get a good estimate of cardinality because one of the predicates is range-based. So the arithmetic will look at the three predicates separately and multiply up their selectivities. (It’s possible, of course, that this might be the first three columns of a 4, or more, column index.)

It’s a reasonable guess that the number of distinct combinations of (puser, name) will be much smaller than num_distinct(puser) * num_distinct(name) – so one strategy that might help increase the table’s cardinality estimate is to create extended statistics on the column group (puser, name).

Another reasonable guess is that the number of distinct values for the two columns is (relatively) small, with some skew to the distribution (name = ‘CODE’ looks particularly susceptible to being a commonly occurring value) – so perhaps we need a histogram on one or both of the columns (which would then require a histogram to be created on the column group as well if we wanted the optimizer to use the column group). We’d also have to make sure that the queried values didn’t fall outside the known low/high values for the columns if we wanted the column group to be used.

STAGE1 Predicates

13 - access("STAGE"."NAME"='XXX' AND "STAGE"."STAT"='I')

This is the access(-only) predicate for the index stage_idx1, and there are no filter predicates when we reach the table. So stage_idx1 might be a two-column index on the table that we are using completely, or it may be an index with more columns that we are using only incompletely. We can see that the cardinality estimate is out by a factor of 12 (84 predicted, 1,000 actual) so if this is the complete index (which would allow Oracle to use the distinct_keys value to estimate cardinality) there must be an uneven data distribution in the values; but if this is just the first two columns of a longer index then we might benefit from extended stats (viz: another column group) on this pair of columns.

Again, even if we create a column group, or take automatic advantage of the distinct_keys figure, the predicate STAT=’I’ (is that state, status?) looks a little dangerous – status columns tend to have a small number of distinct values with a signficant skew to the distribution of values – so we may want to create a histogram on the STAT column, which would then require us to create a histogram on the column group if we also wanted the column group stats to have an effect.

What Happened Next?

I made the suggestions about column groups and histogram to the OP – without the level of detail that I’ve given in the explanations above – and got the following response:

You are spot on.

There does exists frequency histogram on column NAME(having 14 distinct value) and STAT(having 7 distinct values) of table STAGE. Also there already exists a frequency histogram on column PUSER and height balanced histogram on column NAME of table REF1. But still they were not helping the existing query.

By creating a column group on both on ref1 (puser, name) and stage(name, stat) with histogram for both i.e. ‘FOR COLUMNS SIZE 254’. The merge Cartesian removed automatically from the path by optimizer and its doing a hash join now with TAB1 finishing the query in quick time.

Summary

When the cardinality (rows) estimate for an operation drops to one (which might mean much less than one and rounded up) then all hell can break loose and the choice of parent operation – and its cardinality estimate – might be insanely stupid, leading to a catastrophically poor execution plan.

Very low cardinality estimates are often the result of multiplying individual column selectivities to produce an estimated selectivity that is unrealistic (much too small) when compared with the actual number of distinct combinations that exist in the table. In such cases creating a column group, (possibly with a histogram) may be all you need to do to get a better cardinality estimate and a vastly improved execution plan.

 

When Implicit Date Conversions Attack

https://oracle-base.com/blog/wp-content/uploads/2020/07/shark-5135934_64... 300w" sizes="(max-width: 232px) 85vw, 232px" />

Yesterday, one of the developers was having a problem and emailed to ask what was going on. They sent me a section of code from an old trigger that included some date handling that looked “interesting”.

TO_DATE(SYSDATE,'DD/MON/RRRR')

Some bright spark had decided this was the best way to trim the time component off a date, and unfortunately for us it worked for a very, very long time. Many years in fact.

Why was this causing a problem now? They were trying to do an update of a table that caused this to trigger to fire. The update was working fine from SQLcl, but failed when it was called from APEX, giving this error.

ORA-01843: not a valid month

Let’s take a look at what was happening here.

The SYSDATE function returns a date. The TO_DATE function expects a string as input, so Oracle does you a “favour” and does an implicit conversion from date to a string. How does it know how to do this conversion? It uses the NLS_DATE_FORMAT value for the session. What is the default value in our database?

SQL> SELECT SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') FROM dual;

SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')
----------------------------------------
DD-MON-RR

SQL>

OK. So this is what is really happening, thanks to the implicit conversion.

TO_DATE(TO_CHAR(SYSDATE,'DD-MON-RR'),'DD/MON/RRRR')

That’s lucky. Those formats will work with no errors. Phew.

SQL> SELECT TO_DATE(TO_CHAR(SYSDATE,'DD-MON-RR'),'DD/MON/RRRR') FROM dual;

TO_DATE(T
---------
08-JUL-20

SQL>

So what’s the problem with APEX?

If I print out the NLS_DATE_FORMAT value from my APEX session it defaults to ‘DS’. What does that look like?

SQL> SELECT TO_CHAR(SYSDATE,'DS') FROM dual;

TO_CHAR(SY
----------
08/07/2020

SQL>

This is ‘DD/MM/YYYY’. Now I’m hoping you see the problem.

MON = MON
MM <> MON

We can see the result here.

SQL> ALTER SESSION SET nls_date_format = 'DS';

Session altered.

SQL> SELECT TO_DATE(SYSDATE,'DD/MON/RRRR') FROM dual;

Error starting at line : 1 in command -
SELECT TO_DATE(SYSDATE,'DD/MON/RRRR') FROM dual
Error report -
ORA-01843: not a valid month

SQL>

The implicit conversion used a different format mask that happened to be incompatible with the format mask used by the TO_DATE call.

In this case, they should have removed the time component using TRUNC(SYSDATE), but that’s not really the point of this post. Relying on an implicit conversion is *ALWAYS* a bug waiting to happen. In this case it took many years to surface, but the bug was always there. Waiting! APEX didn’t break the code. The code was already broken.

Date handling seems to mystify a lot of people, but it’s not that hard. You just have to pay attention and understand the functions you are using, rather than randomly combining things together until they appear to work.

Cheers

Tim…

PS. If someone says Oracle stores dates as strings, punch them in the face. I take no responsibility for the outcome of this action.

PPS. If you want to know more about Oracle dates, timestamps and intervals, you might want to look at this article.


When Implicit Date Conversions Attack was first posted on July 8, 2020 at 8:12 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Say NO to default DATE formats

Today’s quick but important message is…don’t be lazy!

Look….I get it Smile

If given the choice of typing:


'01-JUL-20'

or typing this:


to_date('01-JUL-20','DD-MON-RR')

then more often than not, we all are going to opt for the first alternative because we all can get lazy. But that reliance on the default date format mask in a session can be fraught with danger. Because a script that worked in one circumstance, can silently fail in another.

Here is how easy it is to fall into this trap.  I’ll start with the expected experience when performing the most simple of operations – just inserting a row:


SQL> create table t ( id int, x date );

Table created.

SQL> alter session set nls_date_format = 'DD-MON-RR';

Session altered.

SQL> insert into t values ( 1,'01-JUL-20');

1 row created.

SQL> select id, to_char(x,'YYYY-MM-DD') from t;

        ID TO_CHAR(X,
---------- ----------
         1 2020-07-01

But what happens if someone has changed the client default settings for NLS_DATE_FORMAT. This could have been easily pushed out via a registry change to your workplace windows machine, or an bash profile change on your Linux machine. And lets face it – how often do we go back and double check those settings? I’ll repeat the same insert with modified NLS settings.


SQL> alter session set nls_date_format = 'DD-MON-YYYY';

Session altered.

SQL> insert into t values (2,'01-JUL-20');

1 row created.

This is one of those examples where the database is almost too “generous” with trying to help us. The format mask is expecting 4 digits for the year; we offered only 2, and the database responded with “No problems!” Smile

No errors, no warnings…until we look at the data.


SQL> select id, to_char(x,'YYYY-MM-DD') from t;

        ID TO_CHAR(X,
---------- ----------
         1 2020-07-01
         2 0020-07-01

If you really do not like using explicit TO_DATE functions whenever you are referring to literal values for dates, at least please use the DATE constructor function, which only allows a single (valid) format of YYYY-MM-DD.


SQL> insert into t values (3,date '2020-07-01');

1 row created.

SQL> select id, to_char(x,'YYYY-MM-DD') from t;

        ID TO_CHAR(X,
---------- ----------
         1 2020-07-01
         2 0020-07-01
         3 2020-07-01