Best way to add column with default value while under load

oracle alter-table
Ben George picture Ben George · May 17, 2012 · Viewed 84.5k times · Source

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

bitmagier picture bitmagier · May 15, 2013

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.

Related questions

how to modify the size of a column

I created the table Test_Project2 in Oracle SQL Developer. After that I realized that the column proj_name is of a small size, so I decided to modify the column using the follwoing statement ALTER TABLE TEST_PROJECT2 MODIFY …

Read More
Alter table to modify default value of column

I have a requirement where we need to modify a column's default value in database table. The table is already an existing table in database and currently the default value of the column is NULL. Now if add a new …

Read More
How do I alter my existing table to create a range partition in Oracle

I have existing table which has 10 years of data (I have taken dump). I would like to Range partition the existing table on one date key column within the table. Most of the examples I see are with CREATE TABLE..…

Read More