Creating NOT NULL Columns in Huge Oracle Tables

June 10, 2004

Databases are often taxed by applying SQL statements to enormous
tables. One such activity is when we add a new NOT NULL column with default
value in a huge transaction table. By 'huge' I mean the number of records. I
will discuss here, the addition of a new column with default value specifically;
however, the methods discussed below can be used for other kinds of batch
processing also.

The main concern in performing such activities is to reduce
the downtime as well as structural changes (privileges, synonyms,
exporting/importing objects, rollback segments, temporary tablespace etc.).
Sometimes we also need to focus on reducing resource utilization (if the
process is required to run online!).

If the concerned table has records numbering in the thousands,
a direct statement would suffice in an acceptable time frame. However, if the
concerned table has records numbering in the millions, then the updates need to
be revisited and the database settings for undo segments and temporary
tablespace sizes need to be considered. A huge DML/DDL activity would take lot
of time and would result in space usage problems and heavy resource
utilization, hence such batch processing is normally scheduled in off peak
hours.

There are many ways of creating a column with a default
value or updating an existing column. Some of the options used to speed up
heavy DML jobs are: using parallel processing, the export/import utility,
partitions or by simply breaking the activity into multiple jobs. Below is
comparison of some of the methods that are commonly used.

For examples in this article, I will make use of a table,
MTL_TRX, present in my database with 2.2 million records. It has a unique index
on the TRX_ID column. I have also specified the approximate time taken by each method;
this may vary according to setup.

Direct Column addition

Look at the following statement.

SQL> alter table mtl_trx add dumind varchar2(1) default 'N' not null;

Adding a new column with a default value takes
considerable time. Here Oracle not only creates the column but also updates it
with the default value; this will generate redo/undo information. Most often,
such statements on massive tables will either hang or abort with an error.

The above statement took about 30-35 minutes to
execute on my system (excluding statistics generation) with properly sized undo
segments.

Advantage: No structural/system changes are done
apart from the column addition, perfect for tables that are not huge!

Once the above updates are complete, the column can
be marked as not null. The complete process takes around 24-26 minutes; this
can be further reduced by running the updates in different sessions. This
option is also used with partitions and in multiple job processing as covered
subsequently.

Advantage: time reduced, No structural/system
changes are done apart from the column addition, commits are possible at
intervals.

Disadvantage: Initial groundwork required for running
the updates, explicit coding is required if the updates are to be run in
parallel.