In Oracle I need to add a new column with type timestamp with time zone to a large table (80 million rows).
I cannot have more than 30 – 60 seconds of downtime when I run it in production.
This statement completes instantly:
alter table
my_table
add my_timestamp_col timestamp(6) with time zone;
However I would like to default the column to current_timestamp at time zone 'UTC' and force it be non-null.
With other data types, Oracle 11g will do this quickly if you use "add column default not null"
For example both of these complete instantly:
alter table
my_table
add my_varchar_col varchar2(10) default 'hello' not null;
alter table
my_table
add my_date_col date default sysdate not null;
But with a timestamp it is slow (at least tens of minutes on my test DB). I assume this means it is updating every row.
So my questions are:
- Why is it different with a timestamp than other data types?
- Is there a fast way to accomplish the outcome where the column is
forced to be non-null and new rows default to the current UTC
timestamp?
Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
Best Answer
This is actually improved in Oracle 12c.
From Oracle Magazine's Ask Tom column: http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53asktom-1999186.html
I know this doesn't help if you're stuck on 11gR2, since you probably want to get that column added before taking an outage to upgrade the DB.
As to why it takes so long only when the data type is
TIMESTAMP
, I'm at a loss. Maybe since theTIMESTAMP
data type is a few more bytes to store thanDATE
, or thanVARCHAR2
for empty strings, and those extra bytes are causing a cascade of row migrations. To test that theory, try adding a fixed length CHAR column (in a non-Prod environment):alter table my_table add my_char_col CHAR(200) default 'hello' not null;
I expect this would not be instantaneous due to row migrations.Your best bet on 11gR2 might be either to
or
DBMS_REDEFINITION
to add the column as part of an online redefinition. This should be non-blocking, and even though it may take hours, the users will not even notice it happening (no locking). If you have a lot of indexes, this becomes a little more difficult. If you have foreign keys, this becomes even more difficult. If you have stored procs or views that reference this table, be sure to recompile them after youFINISH
the redef. This will have a side-benefit that your table will be reorganized (i.e. defragmented).or
If you want to try the Online Redefinition, then you might find these links helpful: