Before 11G adding new columns with default values to tables with millions of rows was a very time consuming and annoying task. Oracle had to update all the rows with the default value when adding the column.
11G introduced a fast=true feature for adding columns with default values. In 11G when you want to add a not null column with a default value, the operation completes in the blink of an eye (if there are no locks on the table of course). Now it does not have to update all the rows, it just keeps the information as meta-data in the dictionary. So adding a new column becomes just a few dictionary operations.
How does this work? How does it return the data if it does not store in the table's blocks?
To answer some questions let's see it in action.
YAS@11G>create table t_default (col1 number);
Table created.
YAS@11G>insert into t_default select 1 from dual;
1 row created.
YAS@11G>commit;
Commit complete.
Now we have a table with one column which has one row in it. Let's add a not null column with a default value.
YAS@11G>alter table t_default add(col_default number default 0 not null);
Table altered.
Now let's have a look at what is in the table's blocks for this one row.
YAS@11G>select dbms_rowid.rowid_block_number(rowid) from t_default where col1=1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
172
YAS@11G>alter system dump datafile 4 block 172;
System altered.
The trace file shows the first column but not the one we added after. It did not update the blocks with the new column's default value.
block_row_dump:
tab 0, row 0, @0x1f92
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 02
end_of_block_dump
If we insert a new row using the default value again.
YAS@11G>insert into t_default values(2,DEFAULT);
1 row created.
YAS@11G>commit;
Commit complete.
YAS@11G>select dbms_rowid.rowid_block_number(rowid) from t_default where col1=2;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
176
YAS@11G>alter system dump datafile 4 block 176;
System altered.
Now the trace file shows the second column also. So Oracle does not update the rows when adding the column. But for subsequent inserts it inserts the default value for the column if no value is supplied. This means the metadata updates are only used when adding the column, after that updates and inserts modify the block for that column also.
block_row_dump:
tab 0, row 0, @0x1f90
tl: 8 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 1] 80
end_of_block_dump
So, what happens when we query the table after adding a not null column with a default value?
Now we know that the rows at the time of the add column operation are not updated, how does it return the value for that column?
Let's do a simple query and see the trace file.
YAS@11G>exec dbms_stats.gather_table_stats(user,'T_DEFAULT');
PL/SQL procedure successfully completed.
YAS@11G>alter session set events '10046 trace name context forever, level 12';
Session altered.
YAS@11G>select * from t_default where col1=1;
COL1 COL_DEFAULT
---------- -----------
1 0
YAS@11G>select * from t_default where col1=2;
COL1 COL_DEFAULT
---------- -----------
2 0
YAS@11G>exit
I have just queried the row which was inserted before the add column operation (whose new column is not updated but stored in the dictionary as metadata) and after that the second row which is inserted after the add column operation (whose new column is updated in the table block).
The part in the raw trace related to the first row shows that just after my query there are some queries from the data dictionary, like the one from ecol$ just below my query.
PARSING IN CURSOR #3 len=36 dep=0 uid=60 oct=3 lid=60 tim=1193743350604471 hv=3796946483 ad='2b3dbc20'
sqlid='dbjjphgj51mjm'
select * from t_default where col1=1
END OF STMT
PARSE #3:c=7998,e=8401,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,tim=1193743350604449
BINDS #3:
EXEC #3:c=0,e=73,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1193743350604794
WAIT #3: nam='SQL*Net message to client' ela= 6 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1193743350604856
FETCH #3:c=0,e=107,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1193743350605014
WAIT #3: nam='SQL*Net message from client' ela= 1304 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1193743350606363
FETCH #3:c=1000,e=992,p=0,cr=4,cu=0,mis=0,r=0,dep=0,og=1,tim=1193743350607750
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=66986 op='TABLE ACCESS FULL T_DEFAULT (cr=7 pr=0 pw=0 time=0 us cost=3 size=5 card=1)'
WAIT #3: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1193743350607916
*** 2007-10-30 13:22:34.800
WAIT #3: nam='SQL*Net message from client' ela= 4192534 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1193743354800488
=====================
PARSING IN CURSOR #3 len=97 dep=1 uid=0 oct=3 lid=0 tim=1193743354801333 hv=2759248297 ad='2b3983f0' sqlid='aa35g82k7dkd9'
select binaryDefVal, length(binaryDefVal) from ecol$ where tabobj# = :1 and colnum = :2
END OF STMT
PARSE #3:c=0,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1193743354801320
BINDS #3:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=002fefb0 bln=22 avl=04 flg=05
value=66986
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=002fefc8 bln=22 avl=02 flg=01
value=2
But the part for the second row does not show those dictionary queries.
PARSING IN CURSOR #4 len=36 dep=0 uid=60 oct=3 lid=60 tim=1193743354802685 hv=704792472 ad='32569228'
sqlid='7dq6h0np04jws'
select * from t_default where col1=2
END OF STMT
PARSE #4:c=1999,e=1898,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,tim=1193743354802668
BINDS #4:
EXEC #4:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1193743354802925
WAIT #4: nam='SQL*Net message to client' ela= 6 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1193743354802987
FETCH #4:c=0,e=119,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,tim=1193743354803159
WAIT #4: nam='SQL*Net message from client' ela= 561 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1193743354803722
FETCH #4:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1193743354803817
STAT #4 id=1 cnt=1 pid=0 pos=1 obj=66986 op='TABLE ACCESS FULL T_DEFAULT (cr=7 pr=0 pw=0 time=0 us cost=3 size=5 card=1)'
WAIT #4: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1193743354803971
*** 2007-10-30 13:22:35.872
WAIT #4: nam='SQL*Net message from client' ela= 1068525 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1193743355872534
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #3 len=234 dep=1 uid=0 oct=6 lid=0 tim=1193743355873381 hv=1907731640 ad='2b3d6300'
sqlid='209fr01svbb5s'
update sys.aud$ set action#=:2, returncode=:3, logoff$time=cast(SYS_EXTRACT_UTC(systimestamp) as date), logoff$pread=:4,
logoff$lread=:5, logoff$lwrite=:6, logoff$dead=:7, sessioncpu=:8 where sessionid=:1 and entryid=1 and action#=100
END OF STMT
So this means if we query the table for rows whose related columns are maintained in the dictionary we get those values not from the table, but from the dictionary. This is explained in the documentation as:
"However, subsequent queries that specify the new column are rewritten so that the default value is returned in the result set."
What is this ecol$ table that the database queries to get the default column value?
ecol$ is a new dictionary table in 11G. It is created in $ORACLE_HOME/rdbms/admin/dcore.bsq which is called from $ORACLE_HOME/rdbms/admin/sql.bsq which contains all scripts used for creating basic dictionary tables for database operation.
In dcore.bsq it is explained as:
"This table is an extension to col$ and is used (for now) to store the default value with which a column was added"
How do these additional dictionary queries effect my queries?
To see if these additional dictionary queries effect the original query performance let's create two tables each with two columns, one with a not null column with a default value added after some rows are inserted, and a copy of it with the columns in place when creating the table. And query these tables and see their trace.
YAS@11G>r
1* drop table t_default
Table dropped.
YAS@11G>create table t_default (col1 number);
Table created.
YAS@11G>insert into t_default select level from dual connect by level<=10000;
10000 rows created.
YAS@11G>commit;
Commit complete.
YAS@11G>alter table t_default add(col_default number default 0 not null);
Table altered.
YAS@11G>exec dbms_stats.gather_table_stats(user,'T_DEFAULT');
PL/SQL procedure successfully completed.
YAS@11G>alter session set events '10046 trace name context forever, level 12';
Session altered.
YAS@11G>begin
2 for t in (select * from t_default) loop
3 null;
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
YAS@11G>exit
I have selected all rows after adding the column. Here is the trace file output. I am just including the part with the original sql and the summary part.
SELECT *
FROM
T_DEFAULT
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 101 0.01 0.01 0 119 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 103 0.02 0.02 0 119 0 10000
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 8 0.00 0.00 0 0 0 0
Execute 8 0.00 0.00 0 3 3 1
Fetch 109 0.01 0.01 0 133 0 10003
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 125 0.02 0.02 0 136 3 10004
Misses in library cache during parse: 1
2 user SQL statements in session.
7 internal SQL statements in session.
9 SQL statements in session.
I have 119 reads for the table and 139-119=20 reads from dictionary tables. I have 7 dictionary sqls each executed one time.
Try the same with a table with the same data which has the columns in place in creation.
YAS@11G>create table t_normal (col1 number,col_default number);
Table created.
YAS@11G>insert into t_normal select level,0 from dual connect by level<=10000;
10000 rows created.
YAS@11G>exec dbms_stats.gather_table_stats(user,'T_NORMAL');
PL/SQL procedure successfully completed.
YAS@11G>commit;
Commit complete.
YAS@11G>alter session set events '10046 trace name context forever, level 12';
Session altered.
YAS@11G>begin
2 for t in (select * from t_normal) loop
null;
end loop;
end;
3 4 5 6
7 /
PL/SQL procedure successfully completed.
YAS@11G>exit
SELECT *
FROM
T_NORMAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 101 0.00 0.01 0 119 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 103 0.01 0.01 0 119 0 10000
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 3 3 1
Fetch 101 0.00 0.01 0 119 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 105 0.01 0.01 0 122 3 10001
Misses in library cache during parse: 1
2 user SQL statements in session.
1 internal SQL statements in session.
3 SQL statements in session.
I have again 119 reads for the table. But now there is only one dictionary sql (which is an update on aud$ which was also there for the former case). I have now 6 dictionary reads instead of 20.
As you can see there is little overhead for querying the dictionary for the default values of columns. No matter how many rows you have, those dictionary sqls are run only once for your query. But the gain you have from this feature is huge, availability is dramatically increased, meaning now you can sleep more instead of waiting for an add column operation.
There are some restrictions for this fast=true switch which are explained here:
"However, the optimized behavior is subject to the following restrictions:
*The table cannot have any LOB columns. It cannot be index-organized, temporary, or part of a cluster. It also cannot be a queue table, an object table, or the container table of a materialized view.
*The column being added cannot be encrypted, and cannot be an object column, nested table column, or a LOB column."
