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!
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 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.