Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> ALTER TABLE ADD column DEFAULT value

ALTER TABLE ADD column DEFAULT value

From: Leslie Tierstein <leslie.tierstein_at_visionchain.com>
Date: Tue, 28 Sep 2004 10:37:47 -0700
Message-ID: <4AE2782C2093B34784E67971035A8160014E5405@ehost011-2.exch011.intermedia.net>


If I were creating a table from scratch, I would include in it a NOT NULL column with a default value. However, the table already exists, without the column that needs to be added.

What does an ALTER TABLE ADD column with a DEFAULT value actually do?

It seems that it alters the table, but, then, within a transaction (so that it gets logged), Oracle issues the command: UPDATE table_name SET column =3D default_value. (verified by looking at V$SQL...)

This is not a situation to be wished for when the table_name in question has many millions of rows.=20

So, in this scenario, what's the best way to end up with the column, with a default value, added to the table definitions? I know that I can add the column as NULL, write a script to insert default values into the column (with appropriately timed commits), and then change the column to NOT NULL. But if I then attempt to alter the table and add the DEFAULT, will Oracle still execute the UPDATE command with no WHERE clause? =20

So, a general question: it seems that ALTER TABLE ADD column DEFAULT not only does DDL, but also DML (and an associated transaction)? And the table isn't altered until the DML completes (since the column can't be created without the default value)?

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Sep 28 2004 - 12:33:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US