_ADD_COL_OPTIM_ENABLED参数

参考自:

Init.ora Parameter "_ADD_COL_OPTIM_ENABLED" [Hidden] Reference Note (文档 ID 1492674.1)

This parameter is set to true by default if Parameter:COMPATIBLE >= 11 . The parameter relates specifically to the actions taken by Oracle for DDL of the form:

ALTER TABLE .. ADD COLUMN ( ... NOT NULL DEFAULT xxx )

In this very specific case of adding a NOT NULL column which has a DEFAULT value then Oracle has 2 options to perform this DDL:

1. Before 11g or if _ADD_COL_OPTIM_ENABLED = FALSE:
   The DDL will cause the column to be added to the table defintion and every row of the table is updated with the new column and its default value. For large tables the update of all rows to add a column can take some time.
2. In 11g onwards provided _ADD_COL_OPTIM_ENABLED = TRUE:
   The DDL adds the column to the table definition but there is no update of the actual table rows. Instead the dictionary stores a flag so that it knows that any row without the column value present should return the DEFAULT value instead. For large tables this can be much faster than option "1". Any code path that accesses the table has to honour the flag otherwise it may not return the correct data for the column.

The setting of the parameter only affects new  ADD COLUMN  commands. Once a column has been added using the optimized method a flag in the dictionary indicates that specific column as being optimized.

You can check which columns have been ADDed with the optimization active using SQL of the form below :

   select owner, object_name, name 
     from dba_objects, col$
    where bitand(col$.PROPERTY,1073741824)=1073741824
      and object_id=obj#;

If you encounter a bug issue related to _ADD_COL_OPTIM_ENABLED then typically any column listed by the above SQL might be affected. A workaround is typically to set _ADD_COL_OPTIM_ENABLED = FALSE and then recreate the problem table so that none of the columns show as optimized.