Creating NOT NULL Columns in Huge Oracle Tables

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

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

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

Disadvantage: Resource intensive, time consuming, undo
segment errors quite common, no commits possible at intervals.

The methods that follow are intended as a work
around for preventing the above disadvantages.

Using an existing index

We can make use of an existing column that has
an index on it for creating a not null column. By using an index, the workload
can be split in multiple updates.

SQL> alter table mtl_trx add chrind varchar2(1);

Table altered.

SQL> update mtl_trx
2 set chrind = ‘L’
3 where trx_date < ’01-jan-04′;

SQL> commit;

SQL> update mtl_trx
2 set chrind = ‘L’
3 where trx_date >= ’01-jan-04′ and trx_date < ’01-feb-04′;

and so on..

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

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

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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles