create table add_column_default ( id number )
/
insert into add_column_default ( id ) values ( 0 )
/
alter table add_column_default add col1 number default 5
/
insert into add_column_default ( id, col1 ) values ( 11, null )
/
select * from add_column_default order by id
/
ID COL1
---------- ----------
0 5
11
2 rows selected.
drop table add_column_default
/Assumptions:My understanding is that the data block is not modified during the ALTER. That is, the row with id = 0 is not updated to add a col1 value of 5.
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-TABLE.html#:~:text=If%20you%20specify%20the%20DEFAULT,subject%20to%20the%20following%20restrictions%3A "If you specify the DEFAULT clause for a column, then the default value is stored as metadata but the column itself is not populated with data. However, subsequent queries that specify the new column are rewritten so that the default value is returned in the result set." Note: This used to not be the case for nullable columns in 11.2.
https://docs.oracle.com/cd/E18283_01/server.112/e17118/statements_3001.htm#CJAHHIBI:~:text=When%20you%20add%20a%20column%2C%20the,a%20default%20value%20or%20NULL "When you add a column, the initial value of each row for the new column is null.
...
If you specify the DEFAULT clause for a nullable column, then the default value is added to existing rows as part of this ALTER TABLE statement, and any update triggers defined on the table are fired. This behavior also results if you change a NOT NULL column with a default value to be nullable."My understanding is that the data block does not store any information (not even the length byte) regarding col1 when the value is null and col1 is the last column in the table.
Therefore:Rows inserted before the ALTER do not have col1 information.
Rows inserted after the ALTER may not have col1 information (inserting null into the last fixed width column in a table).
Confusion/Question:If both rows look the same (with respect to col1 information) in the data block, then how does Oracle know to return a col1 value of 5 for the row with id = 0 and return a col1 value of null for the row with id = 11?
A few scenarios here to look at
1) Add default to nullable column. In this case, all existing rows remain untouched, and new rows pick up the default. No back population required.
We can see this by diving into the block internals, eg
SQL> create table t ( x int, y int );
Table created.
SQL> insert into t (x) values (1);
1 row created.
SQL> alter table t modify y default 10;
Table altered.
SQL> insert into t (x) values (2);
1 row created.
SQL> select * from t;
X Y
---------- ----------
1
2 10
SQL> select file_id from dba_data_files where tablespace_name = 'USERS';
FILE_ID
----------
12
SQL> select dbms_rowid.rowid_block_number(rowid)
2 from t;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
217743
217743
SQL> alter system dump datafile 12 block 217743;
System altered.
SQL> select value
2 from v$diag_info
3 where value like '%trc';
VALUE
----------------------------------------------------------
C:\ORACLE\diag\rdbms\db21\db21\trace\db21_ora_36340.trc
SQL> select payload
2 from v$diag_trace_file_contents
3 where trace_filename = 'db21_ora_36340.trc'
4 and substr(payload,1,3) in ('tab','col');
PAYLOAD
-------------------------------------------------
tab 0, row 0, @0x1f92
col 0: [ 2] c1 02
tab 0, row 1, @0x1f89
col 0: [ 2] c1 03
col 1: [ 2] c1 0b
Notice the first row has only "col 0" , ie a single column. The second row has 2 columns (col 0, col1)
2) Add default non-null column. Once again no backpop required because we store in the table metadata that this column is "automatically present" for all old and new rows
SQL> create table t1 ( x int );
Table created.
SQL> insert into t1 (x) values (1);
1 row created.
SQL> insert into t1 (x) values (2);
1 row created.
SQL> alter table t1 add z int default 10 not null;
Table altered.
SQL> select * from t1;
X Z
---------- ----------
1 10
2 10
SQL>
SQL>
SQL> select dbms_rowid.rowid_block_number(rowid)
2 from t1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
217751
217751
SQL> alter system dump datafile 12 block 217751;
System altered.
SQL>
SQL> select value
2 from v$diag_info
3 where value like '%trc';
VALUE
-----------------------------------------------------------------
C:\ORACLE\diag\rdbms\db21\db21\trace\db21_ora_44036.trc
SQL>
SQL> select payload
2 from v$diag_trace_file_contents
3 where trace_filename = 'db21_ora_44036.trc'
4 and substr(payload,1,3) in ('tab','col');
PAYLOAD
-----------------------------------------------------------------
tab 0, row 0, @0x1f92
col 0: [ 2] c1 02
tab 0, row 1, @0x1f8c
col 0: [ 2] c1 03
Notice that each row still only has 1 column value, even though we are now presenting 2 columns on query
3) Same as (2) but now we want to make the column nullable
SQL> create table t2 ( x int );
Table created.
SQL> insert into t2 (x) values (1);
1 row created.
SQL> insert into t2 (x) values (2);
1 row created.
SQL> alter table t2 add z int default 10 not null;
Table altered.
SQL> select * from t2;
X Z
---------- ----------
1 10
2 10
SQL> alter table t2 modify z null;
Table altered.
SQL> insert into t2 (x) values (3);
1 row created.
SQL> insert into t2 (x) values (4);
1 row created.
SQL>
SQL>
SQL> select dbms_rowid.rowid_block_number(rowid)
2 from t2;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
438727
438727
438727
438727
SQL> alter system dump datafile 12 block 438727;
System altered.
SQL> select value
2 from v$diag_info
3 where value like '%trc';
VALUE
--------------------------------------------------------------------------------------------------
C:\ORACLE\diag\rdbms\db21\db21\trace\db21_ora_45548.trc
SQL>
SQL> select payload
2 from v$diag_trace_file_contents
3 where trace_filename = 'db21_ora_45548.trc'
4 and substr(payload,1,3) in ('tab','col');
PAYLOAD
--------------------------------------------------------------------------------------------------
tab 0, row 0, @0x1f92
col 0: [ 2] c1 02
tab 0, row 1, @0x1f8c
col 0: [ 2] c1 03
tab 0, row 2, @0x1f81
col 0: [ 2] c1 04
col 1: [ 2] c1 0b
col 2: [ 1] 01
tab 0, row 3, @0x1f76
col 0: [ 2] c1 05
col 1: [ 2] c1 0b
col 2: [ 1] 01
Now things look "odd" because we seem to have THREE columns in there.
SQL> select column_name, hidden_column
2 from user_tab_cols
3 where table_name = 'T2';
COLUMN_NAME HID
------------------------------ ---
X NO
Z NO
SYS_NC00003$ YES
We're using that (amongst other things) to keep manage the fact that we've been tinkering with the default values. This is all done to avoid whenever possible the cost of going back and updating existing data.