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 database that had just been upgraded from

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)

   - dynamic sampling used for this statement (level=2)
  • Is actual default value a constant ? I had exactly the same problem with adding column that defaulted to result of the function .
    – a1ex07
    Commented May 11, 2018 at 18:29
  • Yes it is a constant string. Commented May 11, 2018 at 18:32
  • Did you set the compatible parameter to at least after the upgrade? Commented May 11, 2018 at 18:47

1 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
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='' scope=spfile;

System altered.

Elapsed: 00:00:00.00

SQL> shu immediate
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
  • Yep that was it. Can't believe I missed that. Commented May 14, 2018 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.