Oracle 11g Fast Add Column Taking 11 Hours

Question

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)

• a1ex07: Is actual default value a constant ? I had exactly the same problem with adding column that defaulted to result of the function .
• Jeff Bauersfeld: Yes it is a constant string.
• Balazs Papp: Did you set the compatible parameter to at least 11.2.0.0.0 after the upgrade?

Answer 1

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:

SQL> show parameter compa
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offload_compaction string ADAPTIVE
compatible string 10.2.0.5.0
plsql_v2_compatibility boolean FALSE
SQL> alter table t1 add C_DDL number default 42 not null;
Table altered.
Elapsed: 00:00:52.85
SQL> alter system set compatible='11.2.0.0.0' scope=spfile;
System altered.
Elapsed: 00:00:00.00
SQL> shu immediate
startup
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 289407880 bytes
Database Buffers 771751936 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.
SQL> alter table t1 add D_DDL number default 42 not null;
Table altered.
Elapsed: 00:00:00.04

• Jeff Bauersfeld: Yep that was it. Can't believe I missed that.