為什麼在 Oracle 11g 中添加預設不為空的時間戳列這麼慢？

在 Oracle 中，我需要在一個大表（8000 萬行）中添加一個帶時區的時間戳類型的新列。當我在生產中執行它時，我的停機時間不能超過 30 - 60 秒。

此語句立即完成：

alter table
my_table
add my_timestamp_col timestamp(6) with time zone;

但是，我想將該列預設為時區“UTC”的 current_timestamp 並強制它為非空。

對於其他數據類型，如果您使用“添加列預設值不為空”，Oracle 11g 將快速完成此操作。例如，這兩個都立即完成：

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;

但是使用時間戳會很慢（在我的測試數據庫上至少需要幾十分鐘）。我認為這意味著它正在更新每一行。

所以我的問題是：

1. 為什麼時間戳與其他數據類型不同？
2. 有沒有一種快速的方法來完成列被強制為非空並且新行預設為目前 UTC 時間戳的結果？

版本：Oracle 數據庫 11g 企業版版本 11.2.0.3.0 - 64 位生產

這實際上在 Oracle 12c 中得到了改進。

來自 Oracle 雜誌的 Ask Tom 專欄： http ://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53asktom-1999186.html

在 Oracle 數據庫 11g 中，如果某列具有預設值並且被定義為 NOT NULL，則您可以將其快速添加到表中。（Arup Nanda 在 bit.ly/16tQNCh 上對此進行了介紹。）但是，如果您嘗試添加具有預設值的列並且該列允許空值，則 ADD COLUMN 操作可能會花費大量時間，從而生成大量撤消和重做的數量，並在操作期間鎖定整個表。在 Oracle Database 12c 中，時間、捲和鎖定不再是流程的一部分。

我知道如果您卡在 11gR2 上，這將無濟於事，因為您可能希望在中斷升級數據庫之前添加該列。

至於為什麼只有數據類型為 時才需要這麼長時間TIMESTAMP，我很茫然。也許是因為TIMESTAMP數據類型要儲存的字節數比空字元串多DATE，或者比VARCHAR2空字元串多，這些額外的字節會導致級聯的行遷移。要測試該理論，請嘗試添加一個固定長度的 CHAR 列（在非 Prod 環境中）：alter table my_table add my_char_col CHAR(200) default 'hello' not null;由於行遷移，我預計這不會是瞬時的。

您對 11gR2 的最佳選擇可能是

• 在適當的時間進行中斷，您的利益相關者可以添加此列

要麼

• 用於DBMS_REDEFINITION將列添加為線上重新定義的一部分。這應該是非阻塞的，即使可能需要幾個小時，使用者甚至都不會注意到它的發生（沒有鎖定）。如果你有很多索引，這會變得有點困難。如果您有外鍵，這將變得更加困難。如果您儲存了引用此表的過程或視圖，請確保在重新定義後重新編譯它們FINISH。這將有一個附帶好處，即您的表將被重新組織（即碎片整理）。

要麼

• 也許添加沒有 NOT NULL 約束的列。完成後，更改列以添加 NOT NULL 約束。我的直覺是你仍然會在這個過程中的某個地方被鎖定，所以沒有承諾。也許只是一個瘋狂的想法。

如果您想嘗試線上重新定義，那麼您可能會發現這些連結很有幫助：

• http://www.oracle-base.com/articles/11g/online-table-redefinition-enhancements-11gr1.php
• http://docs.oracle.com/cd/E18283_01/server.112/e17120/tables007.htm

在具有 ~2e9 行 Oracle 12.1 的表上測試的快速添加時間戳列（兩個步驟）：但這僅適用於 null。

alter table T1 add C1 timestamp(6) null;

alter table T1 modify (C1 default systimestamp);

引用自：https://dba.stackexchange.com/questions/68217