玉面飞龙的BLOG

When we need to add a new column to an existed very large table during app is accessing, and this column need to have a default value. How will we do it?

Wrong syntax would be

alter table VERY_LARGE_TABLE add (NEW_COLUMN number default 1 null);

Correct syntax would be

alter table VERY_LARGE_TABLE add NEW_COLUMN number NULL;

alter table VERY_LARGE_TABLE modify NEW_COLUMN default 1;

This is because first syntax will update whole table to make all existed rows having NEW_COLUMN=1. Definitely this is a FULL TABLE SCAN and hold lock on this very large table and would block other app.

See more for simple test.

STATISTIC#=134 is redo size,

SQL> select * from v$Mystat where  STATISTIC#=134;
SID STATISTIC#      VALUE
———- ———- ———-
5773        134          0

SQL> alter table b add  (cbaa2 number default 1 null);
Table altered.

SQL> select cbaa2,count(*) from b group by abc;
CBAA2                 COUNT(*)
———- ———-
1                    19028

SQL> select * from v$Mystat where  STATISTIC#=134;
SID STATISTIC#      VALUE
———- ———- ———-
5773        134    5059812

SQL> conn /
Connected.
SQL> alter table b add abc number null;
Table altered.

SQL> alter table b modify abc default 1;
Table altered.

SQL> select abc,count(*) from b group by abc;
ABC   COUNT(*)
———- ———-
19028

SQL> select * from v$Mystat where  STATISTIC#    =134;
SID STATISTIC#      VALUE
———- ———- ———-
6218        134       6556

above test in Oracle 10.2.0.4

When we DDL, we need to think if it’s an ONLINE option, if it would impact others.