ALTER TABLE ADD column DEFAULT value
- From: "Leslie Tierstein" <leslie.tierstein@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 28 Sep 2004 10:37:47 -0700
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)?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
