Oracle 11g Fast Add Column Taking 11 Hours

alter-tableoracleoracle-11g-r2

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:

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