Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 10, 2004

Creating NOT NULL Columns in Huge Oracle Tables

By Amar Kumar Padhi

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.

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM