Written by: ilmarkerm

Category: Blog entry

Published: August 18, 2014

Before 11g if you added a new column to a table then Oracle had to lock and physically update all rows in the table. Possibly a painful process 🙂 11g helped with that a little, giving metadata only default values for NOT NULL columns, so the change was done only in data dictionary, not in the table data.

SQL> create table default_values (
  2    id number primary key
  3  );

Table created.

SQL> insert into default_values values (1);

1 row created.

SQL> alter table default_values add def_w_not_null varchar2(20) default 'THIS IS DEFAULT' not null;

Table altered.

SQL> select * from default_values;

        ID DEF_W_NOT_NULL
---------- --------------------
         1 THIS IS DEFAULT

SQL> select * from table(dbms_xplan.display_cursor(null,null,'projection'));
...
Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "DEFAULT_VALUES"."ID"[NUMBER,22],
       NVL("DEFAULT_VALUES"."DEF_W_NOT_NULL",'THIS IS DEFAULT')[20]
...

So internally Oracle just rewrites the columns with the following expression: NVL(“DEFAULT_VALUES”.”DEF_W_NOT_NULL”,’THIS IS DEFAULT’). Makes sense 🙂

12c now adds the possibility to add metadata only default value for NULL columns also. Lets see what happens:

SQL> alter table default_values add def_w_null varchar2(20) default 'THIS IS DEFAULT2';

Table altered.

SQL> select id, def_w_null from default_values;

        ID DEF_W_NULL
---------- --------------------
         1 THIS IS DEFAULT2

SQL> select * from table(dbms_xplan.display_cursor(null,null,'projection'));
...
Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "ID"[NUMBER,22], DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL
       ,NVL("DEF_W_NULL",'THIS IS DEFAULT2'),'0',NVL("DEF_W_NULL",'THIS IS
       DEFAULT2'),'1',"DEF_W_NULL")[20]

We have much more complex expression now: DECODE(TO_CHAR(SYS_OP_VECBIT(“SYS_NC00003$”,0)),NULL,NVL(“DEF_W_NULL”,’THIS IS DEFAULT2′),’0′,NVL(“DEF_W_NULL”,’THIS IS DEFAULT2′),’1′,”DEF_W_NULL”). And also a new hidden column SYS_NC00003$. What is in there?

SQL> select id, DEF_W_NULL, nvl2(SYS_NC00003$,'is not null','is null') is_hidden_col_null, SYS_NC00003$ FROM default_values;

        ID DEF_W_NULL           IS_HIDDEN_C SYS_NC00003$
---------- -------------------- ----------- --------------------
         1 THIS IS DEFAULT2     is null

SQL> insert into default_values (id, DEF_W_NULL) values (2, null);

1 row created.

SQL> commit;

Commit complete.

SQL> select id, DEF_W_NULL, nvl2(SYS_NC00003$,'is not null','is null') is_hidden_col_null, SYS_NC00003$ FROM default_values;

        ID DEF_W_NULL           IS_HIDDEN_C SYS_NC00003$
---------- -------------------- ----------- --------------------
         1 THIS IS DEFAULT2     is null
         2                      is not null 01

SQL> insert into default_values (id, DEF_W_NULL) values (3, 'some value');

1 row created.

SQL> select id, DEF_W_NULL, nvl2(SYS_NC00003$,'is not null','is null') is_hidden_col_null, SYS_NC00003$ FROM default_values;

        ID DEF_W_NULL           IS_HIDDEN_C SYS_NC00003$
---------- -------------------- ----------- --------------------
         1 THIS IS DEFAULT2     is null
         2                      is not null 01
         3 some value           is not null 01

SQL> insert into default_values (id) values (4);

1 row created.

SQL> select id, DEF_W_NULL, nvl2(SYS_NC00003$,'is not null','is null') is_hidden_col_null, SYS_NC00003$ FROM default_values;

        ID DEF_W_NULL           IS_HIDDEN_C SYS_NC00003$
---------- -------------------- ----------- --------------------
         1 THIS IS DEFAULT2     is null
         2                      is not null 01
         3 some value           is not null 01
         4 THIS IS DEFAULT2     is not null 01

This new column seems to set a bit, whether the value in the column “should be trusted” or replaced with a default. So, all the rows that existed before the new column was added have NULL is this new column, but all the newly modified rows set a specific bit to 1. Looking at the original expression, then there is also a possibility that this bit is set to 0, maybe this newly added column can contain other “row status bits” also?

DECODE (TO_CHAR (sys_op_vecbit ("SYS_NC00003$", 0)),
    NULL, NVL ("DEF_W_NULL", 'THIS IS DEFAULT2'),
    '0', NVL ("DEF_W_NULL", 'THIS IS DEFAULT2'),
    '1', "DEF_W_NULL"
)