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 \ud83d\ude42 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.<\/p>\n
SQL> create table default_values (\n 2 id number primary key\n 3 );\n\nTable created.\n\nSQL> insert into default_values values (1);\n\n1 row created.\n\nSQL> alter table default_values add def_w_not_null varchar2(20) default 'THIS IS DEFAULT' not null;\n\nTable altered.\n\nSQL> select * from default_values;\n\n ID DEF_W_NOT_NULL\n---------- --------------------\n 1 THIS IS DEFAULT\n\nSQL> select * from table(dbms_xplan.display_cursor(null,null,'projection'));\n...\nColumn Projection Information (identified by operation id):\n-----------------------------------------------------------\n\n 1 - \"DEFAULT_VALUES\".\"ID\"[NUMBER,22],\n NVL(\"DEFAULT_VALUES\".\"DEF_W_NOT_NULL\",'THIS IS DEFAULT')[20]\n...\n<\/code><\/pre>\nSo internally Oracle just rewrites the columns with the following expression: NVL(“DEFAULT_VALUES”.”DEF_W_NOT_NULL”,’THIS IS DEFAULT’)<\/strong>. Makes sense \ud83d\ude42<\/p>\n12c now adds the possibility to add metadata only default value for NULL columns also. Lets see what happens:<\/p>\n
SQL> alter table default_values add def_w_null varchar2(20) default 'THIS IS DEFAULT2';\n\nTable altered.\n\nSQL> select id, def_w_null from default_values;\n\n ID DEF_W_NULL\n---------- --------------------\n 1 THIS IS DEFAULT2\n\nSQL> select * from table(dbms_xplan.display_cursor(null,null,'projection'));\n...\nColumn Projection Information (identified by operation id):\n-----------------------------------------------------------\n\n 1 - \"ID\"[NUMBER,22], DECODE(TO_CHAR(SYS_OP_VECBIT(\"SYS_NC00003$\",0)),NULL\n ,NVL(\"DEF_W_NULL\",'THIS IS DEFAULT2'),'0',NVL(\"DEF_W_NULL\",'THIS IS\n DEFAULT2'),'1',\"DEF_W_NULL\")[20]\n<\/code><\/pre>\nWe 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”)<\/strong>. And also a new hidden column SYS_NC00003$<\/strong>. What is in there?<\/p>\nSQL> select id, DEF_W_NULL, nvl2(SYS_NC00003$,'is not null','is null') is_hidden_col_null, SYS_NC00003$ FROM default_values;\n\n ID DEF_W_NULL IS_HIDDEN_C SYS_NC00003$\n---------- -------------------- ----------- --------------------\n 1 THIS IS DEFAULT2 is null\n\nSQL> insert into default_values (id, DEF_W_NULL) values (2, null);\n\n1 row created.\n\nSQL> commit;\n\nCommit complete.\n\nSQL> select id, DEF_W_NULL, nvl2(SYS_NC00003$,'is not null','is null') is_hidden_col_null, SYS_NC00003$ FROM default_values;\n\n ID DEF_W_NULL IS_HIDDEN_C SYS_NC00003$\n---------- -------------------- ----------- --------------------\n 1 THIS IS DEFAULT2 is null\n 2 is not null 01\n\nSQL> insert into default_values (id, DEF_W_NULL) values (3, 'some value');\n\n1 row created.\n\nSQL> select id, DEF_W_NULL, nvl2(SYS_NC00003$,'is not null','is null') is_hidden_col_null, SYS_NC00003$ FROM default_values;\n\n ID DEF_W_NULL IS_HIDDEN_C SYS_NC00003$\n---------- -------------------- ----------- --------------------\n 1 THIS IS DEFAULT2 is null\n 2 is not null 01\n 3 some value is not null 01\n\nSQL> insert into default_values (id) values (4);\n\n1 row created.\n\nSQL> select id, DEF_W_NULL, nvl2(SYS_NC00003$,'is not null','is null') is_hidden_col_null, SYS_NC00003$ FROM default_values;\n\n ID DEF_W_NULL IS_HIDDEN_C SYS_NC00003$\n---------- -------------------- ----------- --------------------\n 1 THIS IS DEFAULT2 is null\n 2 is not null 01\n 3 some value is not null 01\n 4 THIS IS DEFAULT2 is not null 01\n<\/code><\/pre>\nThis 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?<\/p>\n
DECODE (TO_CHAR (sys_op_vecbit (\"SYS_NC00003$\", 0)),\n NULL, NVL (\"DEF_W_NULL\", 'THIS IS DEFAULT2'),\n '0', NVL (\"DEF_W_NULL\", 'THIS IS DEFAULT2'),\n '1', \"DEF_W_NULL\"\n)\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"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 \ud83d\ude42 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[4],"class_list":["post-34","post","type-post","status-publish","format-standard","hentry","category-blog-entry","tag-oracle"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/34","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=34"}],"version-history":[{"count":3,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/34\/revisions"}],"predecessor-version":[{"id":154,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/34\/revisions\/154"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=34"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=34"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=34"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}