3

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:

  1. Why is it different with a timestamp than other data types?
  2. 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

  • Sounds as if every row gets its own timestamp with distinct fractional seconds. How about alter table...add ts timestamp(6) default '2014-06-13 00:00:00'... and once the column is created you update the default to current_timestamp? – AHalvar Jun 13 '14 at 20:14
  • can you add the statement that tooks long. – miracle173 Jun 14 '14 at 2:43
  • @miracle173 the slow statement is alter table my_table add my_timestamp timestamp(6) with time zone default current_timestamp at time zone 'UTC' not null; – Stephen Karger Jun 16 '14 at 15:03
  • @AHalvar you were both wrong and right. It did not give every row its own fractional seconds, I was able to select every row by specifying the hardcoded version of the timestamp it chose (down to the fractional seconds). But, your suggestion to add the column with a hardcoded default and then update the default to current_timestamp worked. Column added fast and later inserts got the correct current timestamp. – Stephen Karger Jun 16 '14 at 18:34
1

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

In Oracle Database 11g you were able to perform a fast add of a column to a table if it had a default value and was defined as NOT NULL. (Arup Nanda has written about this at bit.ly/16tQNCh.) However, if you attempted to add a column with a default value and that column permitted null values, the ADD COLUMN operation could take a significant amount of time, generate a large amount of undo and redo, and lock the entire table for the duration of the operation. In Oracle Database 12c, that time, volume, and locking are no longer part of the process.

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 the TIMESTAMP data type is a few more bytes to store than DATE, or than VARCHAR2 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

  • Take an outage at an appropriate time that is ok with your stakeholders to add this column

or

  • Use 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 you FINISH the redef. This will have a side-benefit that your table will be reorganized (i.e. defragmented).

or

  • Maybe add the column without the NOT NULL constraint. When that finishes, then alter the column to add the NOT NULL constraint. My intuition is that you'll still get locking somewhere in the process, so no promises. Maybe just a crazy idea.

If you want to try the Online Redefinition, then you might find these links helpful:

  • I tested the theory a long char prompting row migrations, but it still handles it quickly. This is fast alter table my_table add my_char_col CHAR(200) default 'hello' not null; Wondered if oracle was cleverly cheating if the default string value was short but it is still fast when specifying a long default value. alter table my_table add my_char_col char(200) default 'aaaaa ... 200 characters ... aaaa' not null; – Stephen Karger Jun 16 '14 at 14:57
  • Thanks for testing the theory. Best to move on to an online redefinition. I've done this on a 300 GB sized table that I needed to reorganize to address some app performance issues. It was going to take 14+ hours to do it as an offline operation, but the business could only give me 3 hours, and that was in exchange for a kidney. So I did it as an online redef, totally in the background, and the application didn't see a blip or experience any locking. Use OEM to set up an online reorg, but don't run it in OEM, just generate the script. Then you can add your new column in the reorg script. – Joshua Huber Jun 16 '14 at 16:12
-1

Fast add timestamp column (two steps) tested on table with ~2e9 rows Oracle 12.1: But this works with null only.

alter table T1 add C1 timestamp(6) null;

alter table T1 modify (C1 default systimestamp);
  • Won't this leave the existing rows with C1 set to NULL? New columns would get the default value, but the existing 80 million rows in the OP's situation. – RDFozz Oct 8 '18 at 15:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.