Question:
Is there an overhead when a add a column default value to a
table?
I want to know the consequences of adding a DEFAULT value to an
existing column in a table.
I understand that when you add a DEFAULT value to a column which is
Nullable, Oracle updates all the NULL values for the column to the
DEFAULT value, generating a lot of undo/redo data.
Is adding a DEFAULT value to a NOT NULL column a problem?
As the column is NOT NULL, an update would not be done, so no
undo/redo data will be generated.
Answer: I can think of only
one case where modifying a table column default value could cause
all rows to be changed.
However, if you were to simultaneously alter the
table to allow NOT NULL and a default, then yes, Oracle might
perform a full-table scan to change all NULL rows.
Try this, and see if it updates the table:
alter table
fred
modify (mycol NOT NULL default
?EMPTY?);
However, I would prefer to use ordinary DML updates . . .
Obviously, this relationship cannot be enforced if you leave
"orphan" appointments, which are not hooked to a patient.
Update fred set mycol = ?EMPTY? where mycol is NULL;
alter
table
fred
modify (mycol NOT NULL);
