RE: ALTER TABLE ADD column DEFAULT value

Just give it a whirl on a small test table. You know how to trace it.

I predict that the ... alter not null .... will have an IS NULL filter in updating the column to the default value. Bug it if not.

I agree this is pretty annoying, but I'm not sure how else Oracle could have implemented this and maintain read consistency.

Okay, you made me think:

With a NULLIS function specification, so that NULL valued columns are interpreted functionally into the DEFAULT value, Oracle could provide for delivery of a default in place of NULL with a small dictionary update instead of immediately ennumerating all the tuples. Then over time you could update/commit sets of rows from NULL to the default value and drop the NULLIS specification. (NULLIS is my name for this behavior, I hope that term isn't already defined for something else.)

If you have the space, the fastest way with the current feature set may be create table as select, and then rename afterwards. At least you can do that without logging, then export the individual table and get a backup before you drop the original. If you have a BORING implementation of SAME you probably
would want to make sure the original and new tables are on non-interfering stripes and *try* to do it when there is a chance that the disk farm behavior may operate more like a single user system. This presumes the required no updates window to the original is acceptable and you can manage that, as well.

Regards,

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Leslie Tierstein Sent: Tuesday, September 28, 2004 1:38 PM To: oracle-l_at_freelists.org
Subject: ALTER TABLE ADD column DEFAULT value

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Sep 28 2004 - 13:15:35 CDT