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

Kiquenet, I'm not a db performance expert but from previous experiences I find measuring perf in the db incredibly difficult due to Oracles internal caches etc. — Ben George, Oct 29 '14 at 10:13
What does this question have to do with NOT NULL? Ie, why is that relevant to the DEFAULT optimization? — Evan Carroll, Aug 29 '18 at 07:01
What about not giving a default value in the DB but rather in the code, something like, `NVL(country, 'GB')`? This will probably very fast from upgrade perspective. — AlikElzin-kilaka, Dec 31 '19 at 10:52

score 23 · Accepted Answer · answered May 15 '13 at 12:25

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.

Found some documentation on the subject in https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables006.htm#ADMIN11005: "For some types of tables (for example, tables without LOB columns), if you specify both a NOT NULL constraint and a default value, the database can optimize the column add operation and greatly reduce the amount of time that the table is locked for DML" — AlikElzin-kilaka, Dec 31 '19 at 11:18

score 0 · Answer 2 · answered May 17 '12 at 10:29

0

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.

answered May 17 '12 at 10:29

Dewfy

23,277
13
73
121

score 0 · Answer 3 · answered May 17 '12 at 10:58

0

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

answered May 17 '12 at 10:58

David Aldridge

51,479
8
68
96

Best way to add column with default value while under load

3 Answers3