Oracle Database Discussions

Hi,

I read about adding NOT NULL values with default values stored as meta data only.

From the SQL manual (ALTER TABLE ADD COLUMN clause):
"When you add a column, the initial value of each row for the new column is null.

If you specify the DEFAULT clause for a NOT NULL column, then the default value is stored as metadata but the column itself is not populated with data. However, subsequent queries that specify the new column are rewritten so that the default value is returned in the result set.

This optimized behavior differs from earlier releases, when as part of the ALTER TABLE operation Oracle Database updated each row in the newly created column with the default value, and then fired any update triggers defined on the table. In this release, no triggers are fired because the default is stored only as metadata. 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."

When I try to use SYS_GUID as default value to a large table (1.000.000 rows) it takes a long time instead of a very short time as supposed. What am I missing?
I have tried to add a column with these two different syntaxes, but both seems to evaluate all the rows:

ALTER TABLE TEST ADD ROWKEY VARCHAR2(50) DEFAULT sys_guid() NOT NULL;

ALTER TABLE TEST ADD ROWKEY raw(50) DEFAULT sys_guid() NOT NULL;

Regards
Håkan