Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

September 28, 2008

11g Enhancement for ALTER TABLE .. ADD COLUMN Functionality

Filed under: Oracle 11g New Features,Oracle Performance — H.Tonguç Yılmaz @ 10:45 pm

Before Oracle 11g adding new columns with DEFAULT values and NOT NULL constraint required both an exclusive lock on the table and the default value to be stored in all existing records.

Now in Oracle 11g the database can optimize the resource usage and storage requirements for this operation, the default value is stored in the data dictionary instead of updating the table column as a result especially for large tables the execution time is reduced and space is saved.

In addition, the following ADD COLUMN operations can now run concurrently with DML operations:
* Add a NOT NULL column with a default value
* Add a nullable column without a default value
* Add a virtual column

release 1002000300 -


drop table tst_source purge ;
create table tst_source nologging as
select rownum id, text
  from dba_source;

set timing on

ALTER TABLE tst_source ADD (name VARCHAR2(16) DEFAULT 'N' NOT NULL);

Elapsed: 00:00:30.43

set timing off

exec dbms_stats.gather_table_stats(user, 'tst_source');

select count(*) from tst_source where name is NULL ;

  COUNT(*)
----------
         0

release 1101000600 –


drop table tst_source purge ;
create table tst_source nologging as
select rownum id, text
  from dba_source;

set timing on

ALTER TABLE tst_source ADD (name VARCHAR2(16) DEFAULT 'N' NOT NULL);

Elapsed: 00:00:00.10

set timing off

exec dbms_stats.gather_table_stats(user, 'tst_source');

select count(*) from tst_source where name is NULL ;

  COUNT(*)
----------
         0
About these ads

5 Comments »

  1. it has some issues however…

    https://metalink.oracle.com/CSP/main/article?cmd=show&type=NOT&id=602327.1

    Comment by laurentschneider — September 29, 2008 @ 7:21 am | Reply

  2. [...] Speaking of performance also brings me to the changes in Oracle 11g, where H.Tonguç Yılmaz talks about enhancements to ALTER TABLE … ADD COLUMN functionality. [...]

    Pingback by Log Buffer #117: a Carnival of the Vanities for DBAs — October 3, 2008 @ 5:52 pm | Reply

  3. [...] and making the column addition instantaneous. An example of this feature at work can be seen in 11g Enhancement for ALTER TABLE .. ADD COLUMN Functionality and some bugs regarding sysdate, as pointed out in the [...]

    Pingback by Pythian Group - Blog — March 20, 2009 @ 8:09 pm | Reply

  4. can I put a column default value as sequence_name.nextval?

    Comment by Nestor — October 29, 2010 @ 5:24 pm | Reply

  5. @Nestor

    If you are trying to populate a pkey column, you can use an after insert trigger for this purpose and call the related sequence inside this trigger.

    Comment by H.Tonguç Yılmaz — October 29, 2010 @ 6:48 pm | Reply


RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 79 other followers

%d bloggers like this: