Systems Engineering and RDBMS

Alter table change in Oracle 11g

Posted by decipherinfosys on March 4, 2009

In 11g, Oracle optimized the behavior of adding a not null column with a default value. When we add a new nullable column to an existing table with data records, for each row, the initial value of the new column is null. If the column is declared as a NOT NULL column with a default value, then it’s default value gets assigned to all the existing records in the system.  If the size of the table is huge, this addition of a new column could take quite some time.

Let us create a test table and populate it with 1 million records.

CREATE TABLE TEST1
(
COL1 VARCHAR2(30) NOT NULL,
COL2 VARCHAR2(30),
COL3 NUMBER(9) DEFAULT 0 NOT NULL
);

BEGIN
FOR I IN 1..1000000
LOOP
INSERT INTO TEST(col1,col2)
VALUES(‘Hello..’||to_char(i),’World..’||to_char(i));
END LOOP;

END;
/

Now let us issue alter table command to add new not null column with a default value. We will set the timing on to measure the time. We have executed this alter statement on a laptop with 2 CPU and 2GB RAM.

/* 10g */
SQL> set timi on
SQL> ALTER TABLE TEST ADD COL4 VARCHAR2(2) DEFAULT ‘NO’ NOT NULL;
Table altered.
Elapsed: 00:00:43.85
SQL>

As our timing indicates, it took about 44 seconds to alter the table for addition of new not null column. Now let us execute same alter statement in 11g. This command was also executed on 2 CPU , 2GB RAM laptop. Make sure that you already have created test table and populated it with same number of records as done in 10g.

/* In Oracle 11g */
SQL> set timi on
SQL> ALTER TABLE TEST ADD COL4 VARCHAR2(2) DEFAULT ‘NO’ NOT NULL;

Table altered.

Elapsed: 00:00:00.20
SQL>

From above results, it is very apparent that in 11g it took less than a second to add the new column with default value in a table with a 1 million rows.  As mentioned in the starting of the post, Oracle changed the behavior of new column addition. In 11g, default value is stored as meta data but column it self is not populated with the default value as opposed to updating value in each row in previous releases. But it does not stop there. All the queries written subsequently which refers to the newly added column are re-written internally in such a way so that it displays the default data value.

There are some restrictions though:

•    The table to which the new column is being added cannot be temporary or index organized table. Also it cannot be a queue table or an object table.
•    The table can not contain any LOB column.
•    The new column cannot be an encrypted column, LOB column, object column or nested table column.

Resources:

Oracle 11g SQL Reference manual – here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: