疑問
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値を変更しても変わらないためと思われる。
データブロック
列追加時にデータブロックがどのように変化するかを別エントリでまとめた。
Oracle Database 12cR1 で列追加した時の行データブロック検証 - ぱと隊長日誌
列追加だけでは行データブロック更新を行わないことがわかる。
更新情報
2015/08/23
列追加時のデータブロックの検証を行ったエントリへのリンクを追加しました。