What is the best approach to add a not null column with default value in production oracle database when that table contain one million records and it is live. Does it create any locks if we do the column creation , adding default value and making it as not null in a single statement?
Asked
Active
Viewed 1,502 times
2
-
If the table is compressed (or it is partitioned and has any compressed partitions or subpartitions) then you can't add a `not null` column in one shot or you'll get *ORA-39726: unsupported add/drop column operation on compressed tables*. If that is the case you'll have to break it out into separate steps (add nullable column without default, update table, modify column). – William Robertson Oct 27 '18 at 11:25
1 Answers
2
Depends on the version. Starting from 11g, you can execute a simple ALTER TABLE command and there will be no impact, the default value is not materialized and taken from dictionary information.
alter table tablename add columnname varchar2(2) default 'XX' not null;
The above statement will not issue an update to all the records of the table [...] When a user selects the column for an existing record, Oracle gets the fact about the default value from the data dictionary [...] and still not incur any penalty for redo and undo generation [...]
Source here, "Adding Columns with a Default Value" chapter
Cee McSharpface
- 8,493
- 3
- 36
- 77
-
From 12.1 we can use the enhanced `default on null` syntax, which also implicitly makes the column `not null`. – William Robertson Oct 27 '18 at 11:23