Tom, I have a doubt about adding columns with default values to a table. Recently, I have stumble upon an Arup Nanda article on it where he states the following

"When a user selects the column for an existing record, Oracle gets the fact about the default value from the data dictionary and returns it to the user" he further says "you can define a new column as not null and with a default value and still not incur any penalty for redo and undo generation."

which actually caught my attention. Reading through the documentation I see this "When you specify a default value, the database immediately updates each row with the default value. Note that this can take some time, and that during the update, there is an exclusive DML lock on the table." It makes no mention of Oracle getting the fact about the default value of the column of the table from the dictionary. I do see, however, that a new sentece has been added to the 11g documentation regarding this and this is it "For some types of tables (for example, tables without LOB columns), if you specify both a NOT NULL constraint and a default value, the database can optimize the column add operation and greatly reduce the amount of time that the table is locked for DML." but again, it doesn't mention what KIND of optimization Oracle does. I have done very light test adding the column with the default value and not null constraint straight, as opposing of doing it step by step (add the column, update to the default value, then add the not null constraint) and whereas redo generation (according to v$mystat) is reduced, there is no significant reduction in undo generation (this is on 10gR2).

So my question is: Is it correct to assume what Arup Nanda says about Oracle getting the fact about the default value of the column at "query time" or Oracle still has to update all the rows when you specify a default value to a column and a not null constraint ?

For your reference, here are the links to Arup Nanda article and 11g documentation

http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-schemamanagement.html
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tables006.htm#sthref1856

Thanks!

ops$tkyte%ORA11GR1> create table t pctfree 50 as select * from all_objects;

Table created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> column redo new_val R
ops$tkyte%ORA11GR1> column undo new_val U
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select max(decode( a.name, 'redo size', b.value )) redo,
  2         max(decode( a.name, 'undo change vector size', b.value )) undo
  3  from v$statname a, v$mystat b
  4  where a.statistic# = b.statistic#
  5  and a.name in ( 'redo size', 'undo change vector size' );

      REDO       UNDO
---------- ----------
 112138396   31140348

ops$tkyte%ORA11GR1> alter table t add y char(80) default 'x' not null;

Table altered.

ops$tkyte%ORA11GR1> select max(decode( a.name, 'redo size', b.value )) redo,
  2         max(decode( a.name, 'redo size', b.value )) -&R redo_diff,
  3         max(decode( a.name, 'undo change vector size', b.value )) undo,
  4         max(decode( a.name, 'undo change vector size', b.value ))-&U undo_diff
  5  from v$statname a, v$mystat b
  6  where a.statistic# = b.statistic#
  7  and a.name in ( 'redo size', 'undo change vector size' );

      REDO  REDO_DIFF       UNDO  UNDO_DIFF
---------- ---------- ---------- ----------
 112147868       9472   31143192       2844

ops$tkyte%ORA11GR1> alter table t add x char(80) default 'x' null;

Table altered.

ops$tkyte%ORA11GR1> select max(decode( a.name, 'redo size', b.value )) redo,
  2         max(decode( a.name, 'redo size', b.value )) -&R redo_diff,
  3         max(decode( a.name, 'undo change vector size', b.value )) undo,
  4         max(decode( a.name, 'undo change vector size', b.value ))-&U undo_diff
  5  from v$statname a, v$mystat b
  6  where a.statistic# = b.statistic#
  7  and a.name in ( 'redo size', 'undo change vector size' );

      REDO  REDO_DIFF       UNDO  UNDO_DIFF
---------- ---------- ---------- ----------
 148759788   36611920   41369292   10226100