oracle12c - Why does Oracle add a hidden column here? -

we migrated customer system oracle 12c , latest version of our product. process includes running number of migration scripts add or change tables. noticed adding column table while providing default value, creates additional hidden column sys_nc00002$.

you should able reproduce following code

create table xxx (a integer); alter table xxx add (b integer default 1);  select table_name, column_name, data_type, data_length, column_id, default_length, data_default user_tab_cols table_name='xxx';  table_name|column_name |data_type|data_length|column_id|default_length|data_default| ------------------------------------------------------------------------------------ xxx       |a           |number   |         22|        1|              |            | xxx       |sys_nc00002$|raw      |        126|         |              |            | xxx       |b           |number   |         22|        2|             1|1           | 

when populate table , @ values in hidden column, same:

select distinct sys_nc00002$ xxx;  sys_nc00002$| ------------- 01          | 

amazingly, when don't set default value right away in statement, no additional hidden column created.

create table xxy (a integer); alter table xxy add (b integer); alter table xxy modify b default 1;  select table_name, column_name, data_type, data_length, column_id, default_length, data_default user_tab_cols table_name='xxy';  table_name|column_name|data_type|data_length|column_id|default_length|data_default| ----------------------------------------------------------------------------------- xxy       |a          |number   |         22|        1|              |            | xxy       |b          |number   |         22|        2|             1|1           | 

can explain hidden column , why created in first example, not in second?

in oracle release 11g oracle has introdused new optimization technique improving performance of ddl operations. new feature allows extremely rapid execution time when adding not null column default value existing table. since release 12c ddl optimization has been extended include null columns having default value.

consider following test table 1.000.000 rows:

sql> create table xxy select rownum dual connect level <= 1e6 ; sql> select /*+ gather_plan_statistics */ count(1) xxy; sql> select * table(dbms_xplan.display_cursor);  

now we're going add not null column having default value in different sessions 11g , 12c:

11g> alter table xxy add b number default 1;      --table xxy altered. elapsed: 00:01:00.998  12c> alter table xxy add b number default 1;      --table xxy altered. elapsed: 00:00:00.052 

notice difference in execution time: 1m rows updated in 5 ms !?

execution plan shows:

11g> select count(1) xxy b = 1;   count(1) ----------    1000000 11g> select * table(dbms_xplan.display_cursor); --------------------------------------------------------------------------- | id  | operation          | name | rows  | bytes | cost (%cpu)| time     | --------------------------------------------------------------------------- |   0 | select statement   |      |       |       |  1040 (100)|          | |   1 |  sort aggregate    |      |     1 |    13 |            |          | |*  2 |   table access full| xxy  |   898k|    11m|  1040   (1)| 00:00:13 | --------------------------------------------------------------------------- predicate information (identified operation id): ---------------------------------------------------    2 - filter("b"=1) note -----    - dynamic sampling used statement (level=2)  12c> select count(1) xxy b = 1; 12c> select * table(dbms_xplan.display_cursor); --------------------------------------------------------------------------- | id  | operation          | name | rows  | bytes | cost (%cpu)| time     | --------------------------------------------------------------------------- |   0 | select statement   |      |       |       |   429 (100)|          | |   1 |  sort aggregate    |      |     1 |     5 |            |          | |*  2 |   table access full| xxy  |  1000k|  4882k|   429   (2)| 00:00:01 | --------------------------------------------------------------------------- predicate information (identified operation id): ---------------------------------------------------    2 - filter(decode(to_char(sys_op_vecbit("sys_nc00002$",0)),null,nvl("               b",1),'0',nvl("b",1),'1',"b")=1) note -----    - statistics feedback used statement 

the execution plan on 12c shows contrast 11g complex predicate part involving new internal column sys_nc00006$.

this predicate indicates that, internally, oracle still considering b column potentially able contain non default values. means - oracle @ first not physically updates each row default value.

why new internal column sys_nc00006$ created?

12c> select column_name, virtual_column, hidden_column, user_generated  user_tab_cols table_name = 'xxy' ; column_name      vir hid use ---------------- --- --- --- b                no  no  yes sys_nc00002$     no  yes no                 no  no  yes  12c> select a, b, sys_nc00002$ hid xxy in (1,10);                   b hid             ---------- ---------- ----------------          1          1                          10          1                   12c> update xxy set b=1 a=10 , b=1; 1 row updated.  12c> select a, b, sys_nc00002$ hid xxy in (1,10);                   b hid             ---------- ---------- ----------------          1          1                          10          1 01               

notice difference in values of b , related internal columns. oracle checking through system generated internal column (e.g. sys_nc00006$) , via sys_op_vecbit function whether consider default value of b column or real value modiefed via explicit dml statement.

what 2 separate alter statements?

12c> alter table xxy add (b integer); 12c> alter table xxy modify b default 1;  12c> select count(b), count(coalesce(b,0)) nulls  xxy b = 1 or b null;    count(b)      nulls ---------- ----------          0    1000000 

the value of new column remains null rows. no real updates needed therefore ddl statement not optimized.

here otn article explains new ddl optimization in more detail.