Best way to add column with default value while under load

Question

When adding a column to a table that has a default value and a constraint of not null. Is it better to run as a single statement or to break it into steps while the database is under load.

ALTER TABLE user ADD country VARCHAR2(4) DEFAULT 'GB' NOT NULL

VERSUS

ALTER TABLE user ADD country VARCHAR2(2)
UPDATE user SET country = 'GB'
COMMIT
ALTER TABLE user MODIFY country DEFAULT 'GB' NOT NULL

Answer 1

Performance depends on the Oracle version you use. Locks are generated anyway.

If version <= Oracle 11.1 then #1 does the same as #2. It is slow anyway. Beginning with Oracle 11.2, Oracle introduced a great optimization for the first statement (one command doing it all). You don't need to change the command - Oracle just behaves differently. It stores the default value only in data dictionary instead of updating each physical row.

But I also have to say, that I encountered some bugs in the past related to this feature (in Oracle 11.2.0.1)

• failure of traditional import if export was done with direct=Y
• merge statement can throw an ORA-600 [13013] (internal oracle error)
• a performance problem in queries using such tables

I think this issues are fixed in current version 11.2.0.3, so I can recommend to use this feature.