12c metadata only default values for NULL columns part 2
- Written by: ilmarkerm
- Category: Blog entry
- Published: November 13, 2014
Yesterday I attended #cluboracle event in London (I highly recommend this event! … but I don’t recommend Thameslink train service 🙂 ). In this event one interesting discussion popped up regarding 12c. I’ve already written about this feature, so in this post I’ll clarify some points as I understand them and test out a few more things.
Here is a quote from my last post summing up my findings so far:
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"
)
Therefore… Only the rows that existed previously don’t have the default value written to the data blocks, all rows added after adding the column have the DEFAULT value written to the data block directly.. What happens if you update the old row?
SQL> create table default_values (
id number primary key
);
Table created.
SQL> insert into default_values values (1);
1 row created.
SQL> insert into default_values values (2);
1 row created.
SQL> insert into default_values values (3);
1 row created.
SQL> insert into default_values values (4);
1 row created.
SQL> alter table default_values add def_w_null varchar2(20) default 'THIS IS DEFAULT2';
Table altered.
SQL> insert into default_values (id) values (5);
1 row created.
SQL> commit;
Commit complete.
Here is the projection information for column DEF_W_NULL.
DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00002$",0)),
NULL,NVL("DEF_W_NULL",'THIS IS DEFAULT2'),
'0',NVL("DEF_W_NULL",'THIS IS DEFAULT2'),
'1',"DEF_W_NULL")
The hidden column containing the bitmap in this test is SYS_NC00002$. Here is the current contents of this column and the interesting bit.
SQL> select id, DEF_W_NULL, nvl2(SYS_NC00002$,'is not null','is null') is_hidden_col_null, SYS_NC00002$, TO_CHAR (sys_op_vecbit(SYS_NC00002$,0)) bitval FROM default_values;
ID DEF_W_NULL IS_HIDDEN_C SYS_NC00002$ BITVAL
---------- -------------------- ----------- ------------- --------
1 THIS IS DEFAULT2 is null
2 THIS IS DEFAULT2 is null
3 THIS IS DEFAULT2 is null
4 THIS IS DEFAULT2 is null
5 THIS IS DEFAULT2 is not null 01 1
All as expected so far. ID 1-4 were added before adding the new column, so SYS_NC00002$ IS NULL for them and the NULL value in file is replaced with metadata default value. ID=5 was added after, so its BITVAL=1 and from the projection information we see, that this column is displayed how it is written to the file. What happens if I update ID=1 row but don’t touch DEF_W_NULL column?
SQL> update default_values set id=10 where id=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select id, DEF_W_NULL, nvl2(SYS_NC00002$,'is not null','is null') is_hidden_col_null, SYS_NC00002$, TO_CHAR (sys_op_vecbit(SYS_NC00002$,0))bitval FROM default_values;
ID DEF_W_NULL IS_HIDDEN_C SYS_NC00002$ BITVAL
---------- -------------------- ----------- ------------- --------
10 THIS IS DEFAULT2 is null
2 THIS IS DEFAULT2 is null
3 THIS IS DEFAULT2 is null
4 THIS IS DEFAULT2 is null
5 THIS IS DEFAULT2 is not null 01 1
Nothing happened with DEF_W_NULL column, Oracle only updated ID column. What if I update DEF_W_NULL column first with keyword DEFAULT and then with the same value?
SQL> update default_values set def_w_null=default, id=11 where id=2;
1 row updated.
SQL> update default_values set def_w_null='THIS IS DEFAULT2', id=12 where id=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> select id, DEF_W_NULL, nvl2(SYS_NC00002$,'is not null','is null') is_hidden_col_null, SYS_NC00002$, TO_CHAR (sys_op_vecbit(SYS_NC00002$,0))bitval FROM default_values;
ID DEF_W_NULL IS_HIDDEN_C SYS_NC00002$ BITVAL
---------- -------------------- ----------- ------------- --------
10 THIS IS DEFAULT2 is null
11 THIS IS DEFAULT2 is not null 01 1
12 THIS IS DEFAULT2 is not null 01 1
4 THIS IS DEFAULT2 is null
5 THIS IS DEFAULT2 is not null 01 1
Oracle has updated the physically stored value, so keep an eye on row migration possibilities!
What happens if I now change the default value in table metadata? Looking at the projection information it must update the existing rows to store the old default value. Lets verify.
SQL> alter table default_values modify DEF_W_NULL varchar2(20) default 'NEW DEFAULT';
Table altered.
SQL> insert into default_values (id) values (6);
1 row created.
SQL> commit;
Commit complete.
SQL> select id, DEF_W_NULL, nvl2(SYS_NC00002$,'is not null','is null') is_hidden_col_null, SYS_NC00002$, TO_CHAR (sys_op_vecbit(SYS_NC00002$,0))bitval FROM default_values;
ID DEF_W_NULL IS_HIDDEN_C SYS_NC00002$ BITVAL
---------- -------------------- ----------- ------------- --------
10 THIS IS DEFAULT2 is null
11 THIS IS DEFAULT2 is not null 01 1
12 THIS IS DEFAULT2 is not null 01 1
4 THIS IS DEFAULT2 is null
5 THIS IS DEFAULT2 is not null 01 1
6 NEW DEFAULT is not null 01 1
Whoa… really interesting. I didn’t do anything! And I think it makes sense now also, it only has to store the initial DEFAULT value, when the column was not present in the table, rows added later already have the correct value written to the files. It must also mean, that Oracle stores two different default values in table metadata – the currently active default value and the initial value when column was added.
The last small thing I want to test, what if I have multiple columns with NULL default values in the same table, does it add one hidden column for each of them?
SQL> alter table default_values add def_w_null2 varchar2(20) default 'THIS IS DEFAULT 3';
Table altered.
SQL> insert into default_values (id) values (6);
1 row created.
SQL> commit;
Commit complete.
SQL> select id, DEF_W_NULL, nvl2(SYS_NC00002$,'is not null','is null') is_hidden_col_null, SYS_NC00002$, TO_CHAR (sys_op_vecbit(SYS_NC00002$,0))bitval1, TO_CHAR (sys_op_vecbit(SYS_NC00002$,1)) bitval2 FROM default_values where id in (6,7);
ID DEF_W_NULL IS_HIDDEN_C SYS_NC00002$ BITVAL1 BITVAL2
---------- -------------------- ----------- ------------- --------- --------
6 NEW DEFAULT is not null 01 1 0
7 NEW DEFAULT is not null 03 1 1
The new column projection information:
DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00002$",1)),
NULL,NVL("DEF_W_NULL2",'THIS IS DEFAULT 3'),
'0',NVL("DEF_W_NULL2",'THIS IS DEFAULT 3'),
'1',"DEF_W_NULL2")
I’m pleasantly surprised again! It did not add a new column, just used another bit in the same column.