Add Column Internal

Yesterday, got one ticket, add column to a table. In order to make sure it wouldn't effect the running application based on that table, I make a "Add Column" experiment  to test its influence.

Conclusion is:
alter table xxx add column nullable is a very light operation. Oracle only update the dictionary table with out making a full table scan. So even a huge big table can finish immediately.
On the contrary,
alter table xxx add column not null, Oracle will scan the segment and check and update all the blocks. It may cost very long time if the table is big. And because it will hold exclusive TM lock, all other operation on that table will be blocked until the "Add Column" command finished.

Following is my experiment :
SQL> conn bbed/oracle
Connected.

SQL> create table xxx (c1 int,c2 char(2));
Table created.

SQL> insert into xxx values(1,'a');
1 row created.

SQL> commit;
Commit complete.

SQL> select dump(rowid,16) from xxx;
DUMP(ROWID,16)
--------------------------------------------------------------------------------
Typ=69 Len=10: 0,0,27,68,1,40,0,f,0,0

#From the rowid we know this row is in file 5 block 15.

SQL> alter system flush buffer_cache;
System altered.

#Check the row data in block. "alter system dump datafile 5 block 15" can't show you the real information.
BBED> set file 5 block 15
FILE# 5
BLOCK# 15

BBED> map
File: /duckdb/oracle/duck/data/bbed01.dbf (5)
Block: 15 Dba:0x0140000f
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @100
struct kdbt[1], 4 bytes @114
sb2 kdbr[1] @118
ub1 freespace[8059] @120
ub1 rowdata[9] @8179
ub4 tailchk @8188

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8179 0x2c

BBED> set offset 8179
OFFSET 8179

BBED> dump
File: /duckdb/oracle/duck/data/bbed01.dbf (5)
Block: 15 Offsets: 8179 to 8191 Dba:0x0140000f
------------------------------------------------------------------------
2c010202 c1020261 20030641 a7
<32 bytes per line>

#Now we add a nullable column.

SQL> alter table xxx add (c3 varchar2(20));
Table altered.

SQL> alter system flush buffer_cache;
System altered.

#Check the row data in block again.

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8179 0x2c

BBED> set offset 8179
OFFSET 8179

BBED> dump
File: /duckdb/oracle/duck/data/bbed01.dbf (5)
Block: 15 Offsets: 8179 to 8191 Dba:0x0140000f
------------------------------------------------------------------------
2c010202 c1020261 20030641 a7

<32 bytes per line>

#We can see the row data is not changed.
#Let's continue to add a not null column.

SQL> alter table xxx add (c4 varchar2(20) default 'dba' not null);
Table altered.

SQL> alter system flush buffer_cache;
System altered.

#Check the row data in block again.

BBED> map
File: /duckdb/oracle/duck/data/bbed01.dbf (5)
Block: 15 Dba:0x0140000f
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @100
struct kdbt[1], 4 bytes @114
sb2 kdbr[1] @118
ub1 freespace[8045] @120
ub1 rowdata[23] @8165
ub4 tailchk @8188

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8165 0x2c

BBED> set offset 8165
OFFSET 8165

BBED> dump
File: /duckdb/oracle/duck/data/bbed01.dbf (5)
Block: 15 Offsets: 8165 to 8191 Dba:0x0140000f
------------------------------------------------------------------------
2c020402 c1020261 20ff0364 62612c00 0202c102 02612001 06c4a8

<32 bytes per line>

#We can see the oracle had change the row data on the block from "2c00 0202c102 026120" to "2c020402 c1020261 20ff0364 6261".


Now we know, if we want to add a column in production database, we had better choose nullable column.

Some people may got a question.  If oracle add nullable column without modify block, then what will happen when oracle want to read data from block?
Oracle will do in this way: when read row data from block and only find tow columns, meanwhile oracle know the table should have 4 columns from col$, then oracle will add value "null" to following tow columns.