DDL Optimization in 12c

In Oracle 11g DDL Optimization has designed to include NOT NULL columns having Default values. In Oracle 12c, DDL Optimization has been extended to include NULL columns having Default values.

In 11g (11.2.0.4) database it took about 1 min to update 1M rows.

rajesh@ORA11G> set timing on

rajesh@ORA11G> alter table big_table add z number default 55;

 

Table altered.

 

Elapsed: 00:01:16.86

rajesh@ORA11G>

 

Where as in 12c database it took less than one second.

 

rajesh@PDB1> set timing on

rajesh@PDB1> alter table big_table add z number default 55;

 

Table altered.

 

Elapsed: 00:00:01.43

rajesh@PDB1>

 

This is a clear demonstration that in Oracle Database 12c, DDL optimization has been extended to include null columns having default values. Indeed, when you query BIG_TABLE table to get the distinct values of the newly added column (Z) you will realize that the entire table rows have seen their metadata (default value 55) updated as shown via the following query

 

rajesh@PDB1> set serveroutput off

rajesh@PDB1> select count(*) from big_table where z = 55;

 

  COUNT(*)

----------

   1000000

 

1 row selected.

 

Elapsed: 00:00:02.63

rajesh@PDB1> select * from table( dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------

SQL_ID  8wkg71rzrsdnn, child number 0

-------------------------------------

select count(*) from big_table where z = 55

 

Plan hash value: 599409829

 

--------------------------------------------------------------------------------

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           |       |       |   435 (100)|          |

|   1 |  SORT AGGREGATE    |           |     1 |    13 |            |          |

|*  2 |   TABLE ACCESS FULL| BIG_TABLE |   893 | 11609 |   435   (1)| 00:00:01 |

--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00020$",0)),NULL,NVL("

              Z",55),'0',NVL("Z",55),'1',"Z")=55)

 

 

20 rows selected.

 

rajesh@PDB1>

 

However, in order to ensure DDL optimization for null columns with default value, things became more complex than it used to be for not null columns in the preceding release.

 

We went to a complex and exotic predicate part involving SYS_OP_VECBIT Oracle non documented function and a new internal column SYS_NC00020$ in order to honor the default value since this one has not been physically updated.

 

In contrast to what you might immediately think of, the SYS_NC00020$ column is not a virtual column. It represents a hidden system generated column as shown below:

 

rajesh@PDB1> select hidden_column,virtual_column,user_generated

  2  from user_tab_cols

  3  where table_name ='BIG_TABLE'

  4  and column_name ='SYS_NC00020$' ;

 

HID VIR USE

--- --- ---

YES NO  NO

 

1 row selected.

 

rajesh@PDB1>

 

 

rajesh@PDB1> create table t1(x int,y date);

 

Table created.

 

rajesh@PDB1> insert into t1(x,y) values(1,sysdate);

 

1 row created.

 

rajesh@PDB1> alter table t1 add z int default 5;

 

Table altered.

 

rajesh@PDB1> column column_name format a15

rajesh@PDB1> select column_name,hidden_column,virtual_column,user_generated

  2  from user_tab_cols

  3  where table_name ='T1' ;

 

COLUMN_NAME     HID VIR USE

--------------- --- --- ---

Z               NO  NO  YES

SYS_NC00003$    YES NO  NO

Y               NO  NO  YES

X               NO  NO  YES

 

4 rows selected.

 

rajesh@PDB1>

 

Even though that this column is hidden it doesn’t pre-empt us from selecting it

 

rajesh@PDB1> select TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)) txt, z

  2  from t1

  3  where z = 5;

 

TXT                 Z

---------- ----------

                    5

 

1 row selected.

 

rajesh@PDB1>

 

The SYS_NC00003$ column will remain null until the Z column will be given a value that is not equal to the default value 5. Consider the following inserts:

 

rajesh@PDB1> insert into t1(x,y,z) values(2,sysdate,150);

 

1 row created.

 

rajesh@PDB1> insert into t1(x,y,z) values(3,sysdate,180);

 

1 row created.

 

rajesh@PDB1> insert into t1(x,y,z) values(4,sysdate,null);

 

1 row created.

 

rajesh@PDB1> commit;

 

Commit complete.

 

rajesh@PDB1>

rajesh@PDB1> select TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)) txt, z

  2  from t1

  3  where z in (150,180)

  4  union all

  5  select TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)) txt, z

  6  from t1

  7  where z is null

  8  union all

  9  select TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)) txt, z

 10  from t1

 11  where z = 5;

 

TXT                 Z

---------- ----------

1                 150

1                 180

1          {null}

{null}              5

 

4 rows selected.

 

rajesh@PDB1>

 

Notice how the SYS_NC00003$ hidden column value is no longer NULL when we insert a non-default value into the Z column (including the explicit NULL values)

 

Putting together the different pieces of the puzzle, Oracle is simply checking through its system generated column and via the SYS_OP_VECBIT function whether to consider the default value of the Z column or the real value introduced by an end user or via an explicit insert statement.

 

There are 4 distinct values of Z column, the default one (5) and 3 explicitly inserted values 180,150 and NULL. When you use a predicate against the Z column to retrieve a row from a table block, the Oracle CBO will decode the above TXT value (based on SYS_ NC00003$) to check its value against your input bind (or literal) variable. As such it can mimic correctly all the values of Z column including those having a default value (5) and which have not been physically updated to reflect this default value.