Oracle Databaseでdefault指定の列を追加するときの挙動

疑問

Oracle Databaseでdefault指定の列を追加すると、長時間のLibrary chache lockが発生すると聞く。これは本当か？

結論

11gR1より前のバージョンでは起こりえた。行追加により既存行が全て更新されるため。

11gR1-11gR2ではdefault指定かつNOT NULL制約の列追加であれば発生しない。ただし、追加した列のNOT NULL制約を外すと既存行がデフォルト値で更新される。

12cは列追加時のNOT NULL制約が必要なくなり、NOT NULL制約を外したとしても既存行更新は発生しない。

説明

10gR2以前のバージョン

default指定の列を追加すると、既存の行はDEFAULT句で指定した値で更新される。これは10gR2のマニュアルの中でも触れられている。

列を追加した場合、DEFAULT句を指定しないかぎり、新しい列の各行には初期値としてNULLが設定されます。DEFAULT句を指定した場合、新しい列の各行は、DEFAULTで指定した値で更新されます。この更新操作によって、表に定義されたAFTER UPDATEトリガーが起動します。
SQL文: ALTER TABLE〜ALTER TABLESPACE

11gR1-11gR2

default指定かつNOT NULL制約の列追加の場合、デフォルト値はメタデータにのみ格納され、データブロックには変更を加えない。これは処理の高速化を図るため。
ただし、以下の場合はこの高速化が適用されず、既存の行が更新される。
・default指定かつNULLが可能な列を追加した
・デフォルト値が含まれるNOT NULL列をNULLであることが可能な列に変更した

NOT NULL列にDEFAULT句を指定すると、デフォルト値はメタデータとして格納されますが、列自体にはデータは移入されません。ただし、デフォルト値が結果セットに戻されるように、新しい列を指定する後続の問合せは再書込みされます。
この最適化された動作は、以前のリリースとは異なります(以前のリリースでは、ALTER TABLE操作の中で、新規作成された列の各行がデフォルト値に更新され、その後で、表に対して定義されている更新トリガーが起動されていました)。このリリースでは、トリガーは起動されません(デフォルト値はメタデータとしてのみ格納されるため)。この最適化された動作には、次の制限事項があります。
（制限事項について省略）
NULLであることが可能な列に対してDEFAULT句を指定すると、デフォルト値がこのALTER TABLE文の一部として既存の行に追加され、表に定義された更新トリガーが起動されます。この動作は、デフォルト値が含まれるNOT NULL列をNULLであることが可能な列に変更した場合にも発生します。
ALTER TABLE

盲点となりやすいのが「デフォルト値が含まれるNOT NULL列をNULLであることが可能な列に変更した」ケース。1億行を超えるテーブルでdefault指定かつNOT NULL制約の列を追加した後にNOT NULL制約を外したために長時間のLibrary chache lockが発生してしまったことを報告しているエントリがある。
http://www.orachrome.com/en/librarycachelock.html
このケースでは検証環境でテストを行っていたが、datapumpによるexport/importを行っており、デフォルト値がメタデータとしてでなく各行に格納されていたため、発覚しなかったとのこと。

12cR1

default指定の列追加の場合、NOT NULL制約の有無にかかわらず、デフォルト値はメタデータにのみ格納され、データブロックには変更を加えない、という処理の高速化が行われた。

列にDEFAULT句を指定すると、デフォルト値はメタデータとして格納されますが、列自体にはデータは移入されません。ただし、デフォルト値が結果セットに戻されるように、新しい列を指定する後続の問合せは再書込みされます。この最適化された動作には、次の制限事項があります。
（以降、省略）
ALTER TABLE

デフォルト値のメタデータはecol$に格納されている。
内部処理については以下資料のスライド「メタデータのみのデフォルト値」を参考のこと。
http://www.oracle.com/jp/corporate/branch/20140115-database12c-summary-2105209-ja.pdf

ecol$の定義は以下のファイルに含まれている。
$ORACLE_HOME/rdbms/admin/dcore.bsq
ファイル内でecol$は以下のように説明されている。

This table is an extension to col$ and is used (for now) to store the default value with which a column was added

参考：Oracle Today: fast=true for adding columns in 11G

ちなみにcol$も同じファイルで定義されており、ファイル内の説明は以下の通り。

Logminer/Streams uses contents of this table.
Please do not reuse any flags without verifying the impact of your changes on inter-op.

検証

Oracle Database 12cでdefault指定の列追加がメタデータのみ更新されることを確認する。処理の高速化については検証環境に依存するため検証しない。

検証環境

Hyper-V上の仮想マシン（メモリ：2048MB）
Oracle Linux Server release 6.6
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

検証用に作成したTRYユーザ（TRYスキーマ）で検証を行う。

テーブルの使用ブロック数

デフォルト値がメタデータにのみ格納されるのであれば、列を追加してもテーブルの使用ブロック数は増加しないことが想定される。これを確認する。

(1) テーブル作成・データ投入

SQL> create table TABLE_WITH_OPTIM as select rownum ID, sysdate DT from dual connect by level < 1000001;

表が作成されました。

SQL> execute dbms_stats.gather_table_stats('TRY', 'TABLE_WITH_OPTIM');

PL/SQLプロシージャが正常に完了しました。

SQL> select blocks from user_tables where table_name = 'TABLE_WITH_OPTIM';

    BLOCKS
----------
      2535

USER_TABLESビューの列は（OWNER列を除き）、ALL_TABLESの列と同じ。ALL_TABLESビューBLOCKS列はDBMS_STATSパッケージを使用して表の統計情報を収集した場合に限り移入される。そのため、GATHER_TABLE_STATSプロシージャを実行している。

参考：Oracle Database 12cR1のドキュメント
USER_TABLES
ALL_TABLES
DBMS_STATS

(2) デフォルト値指定の列追加

SQL> alter table TABLE_WITH_OPTIM add NEW_COLUMN number default 0;

表が変更されました。

SQL> execute dbms_stats.gather_table_stats('TRY', 'TABLE_WITH_OPTIM');

PL/SQLプロシージャが正常に完了しました。

SQL> select blocks from user_tables where table_name = 'TABLE_WITH_OPTIM';

    BLOCKS
----------
      2535

列追加前後で比較すると、表内の使用されたデータ・ブロック数に変化の無いことがわかる。
これにより、default指定の列を追加してもメタデータのみ追加され、既存行は追加されていないとわかる。

(3) 追加した列への更新

SQL> update TABLE_WITH_OPTIM set NEW_COLUMN = 0;

1000000行が更新されました。

SQL> execute dbms_stats.gather_table_stats('TRY', 'TABLE_WITH_OPTIM');

PL/SQLプロシージャが正常に完了しました。

SQL> select blocks from dba_tables where table_name = 'TABLE_WITH_OPTIM';

    BLOCKS
----------
      3946

更新前後で比較すると、表内の使用されたデータ・ブロック数が増加している。
デフォルト値と同じ値での更新であっても、メタデータを参照するのではなく、既存行に追加されている。

ecol$の更新

ecol$にメタデータがどのように格納されるかを確認する。

(1) テーブル作成・データ投入

SQL> create table TABLE_WITH_OPTIM as select rownum ID, sysdate DT from dual connect by level < 1000001;

表が作成されました。

SQL> select * from sys.ecol$;

レコードが選択されませんでした。

(2) デフォルト値指定の列追加

SQL> alter table TABLE_WITH_OPTIM add NEW_COLUMN number default 0;

表が変更されました。

SQL> select * from sys.ecol$;

 TABOBJ#  COLNUM  BINARYDEFVAL   GUARD_ID
-------- ------- ------------- ----------
   92102       4            80          0

SQL> select OBJECT_ID from ALL_OBJECTS where OBJECT_NAME = 'TABLE_WITH_OPTIM';

 OBJECT_ID
----------
     92102

ecol$表に行が追加されている。
ecol$表TABOBJ#列はALL_OBJECTS表OBJECT_ID列と対応しているようだ。

(3) デフォルト値の変更

SQL> alter table TABLE_WITH_OPTIM add NEW_COLUMN_1 number default 1;

表が変更されました。

SQL> alter table TABLE_WITH_OPTIM add NEW_COLUMN_2 number default 1;

表が変更されました。

SQL> alter table TABLE_WITH_OPTIM modify NEW_COLUMN number default 1;

表が変更されました。

SQL> select * from sys.ecol$ order by TABOBJ#, COLNUM;

 TABOBJ#  COLNUM  BINARYDEFVAL   GUARD_ID
-------- ------- ------------- ----------
   92102       4            80          0
   92102       5          C102          1
   92102       6          C102          2

この結果より、列追加時に同じdefault値を指定すると同じBINARYDEFVALになることが推測される。
また、列追加時と別のdefault値に変更したとしても、BINARYDEFVALは変わらない。これは列の追加時点のdefault値で既存行に追加される列の値は決まり、その後default値を変更しても変わらないためと思われる。