CODEWITHSUNDEEP
oracle-databasealter-table
Ben George
Ben George

Reputation: 1005

Best way to add column with default value while under load

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

Upvotes: 33

Views: 100166

Answers (3)

bitmagier
bitmagier

Reputation: 720

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.

Upvotes: 23

David Aldridge
David Aldridge

Reputation: 52396

If your system needs to be using the table then DBMS_Redefinition is really your only choice.

Upvotes: 0

Dewfy
Dewfy

Reputation: 23644

Some time ago we have evaluated possible solutions of the same problem. On our project we had to remove all indexes on table, perform altering and restore indexes back.

Upvotes: 0

Related Questions