To the best of my understanding, a new feature of Oracle 11g is the ability to add a not null column with a default value very quickly as it would no longer auto-update every row in the table with the default value in the new column. However, the following command takes ~11 hours on a table with ~1.2 billion rows on an 11.2.0.4 database that had just been upgraded from 10.2.0.5:
alter table table_name add column_name varchar2(6) default "DEFLT' not null;
Although I've seen the following in the documentation:
"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."
to the best of my knowledge my table does not meet any of these conditions. Describe shows no LOBs, ORGANIZATION INDEX isn't in the create statement, it isn't a temporary table, isn't a cluster, queue, object, or container table. And the column of course is a varchar column. Is there something I'm missing, or is the answer just that I'm mistaken about my table meeting one of these requirements?
ETA: Not sure if this is helpful, but I noticed in a paper that the tell-tell sign of fast column adds is the use of NVL in the filter predicate. From an explain plan on a test table, it does not appear that my database is executing fast adds:
SQL_ID f78gwf6cz50uq, child number 0
-------------------------------------
select count(1) from t where z = 123456
Plan hash value: 1842905362
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 133 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| T | 110K| 1406K| 133 (4)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("Z"=123456)
Note
-----
- dynamic sampling used for this statement (level=2)
Best Answer
Ok, I sacrificed one of my 10.2 sandbox databases for a good cause, and upgraded it to 11.2.
As I suspected, the above DDL optimization does not work with compatible parameter still set to '10.2.*'.
After increasing compatible parameter, it works as intended: