目的
Oracle Database 12cR1 で列追加を行った時、行データブロックがどのように変化するかを検証する。
予備知識
以下のエントリを読んでおくと理解しやすい。
Oracle Databaseでdefault指定の列を追加するときの挙動 - ぱと隊長日誌
Oracle Databaseのバージョンによって、default指定列追加時の処理が異なることを説明している。
Oracle Database のデータ・ブロックのダンプとその解析 - ぱと隊長日誌
データブロックのダンプ方法とその解析の説明。
特にデータブロックの説明部分を読んでおくと理解しやすい。
検証内容
検証環境
Hyper-V上の仮想マシン（メモリ：2048MB）
Oracle Linux Server release 6.7
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
検証用に作成したTRYユーザ（TRYスキーマ）で検証を行う。
データブロックの比較にはWinMerge(version 2.14.0.80+-jp-80)を利用した。
検証手順
以下のケースで比較・検証を行う。
- 追加列の制約
- default無し
- default＋not null
- default＋null可
- 行データ
- (1) 既存行
- (2) 既存行＋追加列のupdate
- (3) 新規行（追加列の値指定無し）
- (4) 新規行（追加列の値指定有り）
- (5) 新規行（追加列の値にnull指定）
検証は以下の流れで実施する。
- 検証対象のテーブルを作成する。
- 初期データ(行データの1,2)をinsertする。
- 行データブロックをダンプする(A)。
- 列を追加する。
- 行データブロックをダンプする(B)。
- 初期データ(行データの2)のupdate、及び追加データ(行データの3,4,5)をinsertする。
- 行データブロックをダンプする(C)。
行データブロックのdiff画像は左からA/B/Cの順に並べている。
実行コマンドについて
今回、見やすさを考慮してコマンドの実行結果を省いている。試す場合は環境に依存する項目（例えば、ブロック番号など）を適宜書き換えること。
検証結果
追加列：default無し
実行コマンド
drop table ADD_COL_NONE_DEF_TBL cascade constraints purge;
create table ADD_COL_NONE_DEF_TBL (ID NUMBER, COL1 VARCHAR2(5));
insert into ADD_COL_NONE_DEF_TBL (ID, COL1) values (1, 'AAAAA');
insert into ADD_COL_NONE_DEF_TBL (ID, COL1) values (2, 'AAAAA');
commit;
alter system checkpoint;
select
dbms_rowid.rowid_to_absolute_fno(rowid, 'TRY', 'ADD_COL_NONE_DEF_TBL') file_id,
dbms_rowid.rowid_block_number(rowid) block_id
from ADD_COL_NONE_DEF_TBL;
alter session set TRACEFILE_IDENTIFIER = 'none_def_init';
alter system dump datafile 6 block 245;
alter table ADD_COL_NONE_DEF_TBL add COL2 VARCHAR2(5);
alter system checkpoint;
alter session set TRACEFILE_IDENTIFIER = 'none_def_coladd';
alter system dump datafile 6 block 245;
update ADD_COL_NONE_DEF_TBL set COL2 = 'BBBBB' where ID = 2;
insert into ADD_COL_NONE_DEF_TBL (ID, COL1) values (3, 'AAAAA');
insert into ADD_COL_NONE_DEF_TBL (ID, COL1, COL2) values (4, 'AAAAA', 'BBBBB');
insert into ADD_COL_NONE_DEF_TBL (ID, COL1, COL2) values (5, 'AAAAA', null);
commit;
alter system checkpoint;
alter session set TRACEFILE_IDENTIFIER = 'none_def_modify';
alter system dump datafile 6 block 245;
実行結果
テーブルの列名とデータブロックの列データの対応は以下の通り。
| 列名 | 列データ |
|---|---|
| ID | col 0 |
| COL 1 | col 1 |
| COL 2 | col 2 |
既存行(ID=1)にCOL2列を追加しただけではデータブロックに変化はない。
既存行(ID=2)のCOL2列の値を更新した場合、対応する列データがデータブロックに追加される。
追加行(ID=4)でCOL2列の値がnull以外の場合、対応する列データがデータブロックに格納される。
追加行(ID=3,5)でもCOL2列の値がnullの場合、対応する列データがデータブロックに格納されない。この仕様はOracle Databaseのリファレンスにも記載されている。
データ値を持つ2つの列の間にはさまれたNULLはデータベースに格納されます。このような場合、NULLには列の長さ(ゼロ)を格納する1バイトのみが必要となります。新しい行のヘッダーが前行の残りの列がNULLであることを示すため、行の末尾にあるNULLには記憶域は不要です。たとえば、表の最後の3列がNULLであれば、その3列にはデータは格納されません。
表と表クラスタ
追加列：default＋not null
実行コマンド
drop table ADD_COL_DEF_NN_TBL cascade constraints purge;
create table ADD_COL_DEF_NN_TBL (ID NUMBER, COL1 VARCHAR2(5));
insert into ADD_COL_DEF_NN_TBL (ID, COL1) values (1, 'AAAAA');
insert into ADD_COL_DEF_NN_TBL (ID, COL1) values (2, 'AAAAA');
commit;
alter system checkpoint;
select
dbms_rowid.rowid_to_absolute_fno(rowid, 'TRY', 'ADD_COL_DEF_NN_TBL') file_id,
dbms_rowid.rowid_block_number(rowid) block_id
from ADD_COL_DEF_NN_TBL;
alter session set TRACEFILE_IDENTIFIER = 'def_nn_init';
alter system dump datafile 6 block 253;
alter table ADD_COL_DEF_NN_TBL add COL2 VARCHAR2(5) default 'BBBBB' not null;
alter system checkpoint;
alter session set TRACEFILE_IDENTIFIER = 'def_nn_coladd';
alter system dump datafile 6 block 253;
update ADD_COL_DEF_NN_TBL set COL2 = 'BBBBB' where ID = 2;
insert into ADD_COL_DEF_NN_TBL (ID, COL1) values (3, 'AAAAA');
insert into ADD_COL_DEF_NN_TBL (ID, COL1, COL2) values (4, 'AAAAA', 'BBBBB');
commit;
alter system checkpoint;
alter session set TRACEFILE_IDENTIFIER = 'def_nn_modify';
alter system dump datafile 6 block 253;
実行結果
テーブルの列名とデータブロックの列データの対応は以下の通り。
| 列名 | 列データ |
|---|---|
| ID | col 0 |
| COL 1 | col 1 |
| COL 2 | col 2 |
既存行(ID=1)にCOL2列を追加しただけではデータブロックに変化はない。
既存行(ID=2)のCOL2列の値を更新をした場合、対応する列データがデータブロックに追加される。
追加行(ID=3,4)はCOL2列に対応する列データがデータブロックに格納される。insert時に値を省略した場合はデフォルト値が格納される。
なお、ここでみた挙動は11gで導入されたdefault指定かつNOT NULL制約の列追加の処理高速化によるものとなる。
Oracle Databaseでdefault指定の列を追加するときの挙動 - ぱと隊長日誌
Oracle Today: fast=true for adding columns in 11G
追加列：default＋null可
実行コマンド
drop table ADD_COL_DEF_NULL_TBL cascade constraints purge;
create table ADD_COL_DEF_NULL_TBL (ID NUMBER, COL1 VARCHAR2(5));
insert into ADD_COL_DEF_NULL_TBL (ID, COL1) values (1, 'AAAAA');
insert into ADD_COL_DEF_NULL_TBL (ID, COL1) values (2, 'AAAAA');
commit;
alter system checkpoint;
select
dbms_rowid.rowid_to_absolute_fno(rowid, 'TRY', 'ADD_COL_DEF_NULL_TBL') file_id,
dbms_rowid.rowid_block_number(rowid) block_id
from ADD_COL_DEF_NULL_TBL;
alter session set TRACEFILE_IDENTIFIER = 'def_null_init';
alter system dump datafile 6 block 261;
alter table ADD_COL_DEF_NULL_TBL add COL2 VARCHAR2(5) default 'BBBBB';
alter system checkpoint;
alter session set TRACEFILE_IDENTIFIER = 'def_null_coladd';
alter system dump datafile 6 block 261;
update ADD_COL_DEF_NULL_TBL set COL2 = 'BBBBB' where ID = 2;
insert into ADD_COL_DEF_NULL_TBL (ID, COL1) values (3, 'AAAAA');
insert into ADD_COL_DEF_NULL_TBL (ID, COL1, COL2) values (4, 'AAAAA', 'BBBBB');
insert into ADD_COL_DEF_NULL_TBL (ID, COL1, COL2) values (5, 'AAAAA', null);
commit;
alter system checkpoint;
alter session set TRACEFILE_IDENTIFIER = 'def_null_modify';
alter system dump datafile 6 block 261;
実行結果
テーブルの列名とデータブロックの列データの対応は以下の通り。
| 列名 | 列データ |
|---|---|
| ID | col 0 |
| COL 1 | col 1 |
| （非表示列） | col 2 |
| COL 2 | col 3 |
既存行(ID=1)にCOL2列を追加しただけではデータブロックに変化はない。
COL2列にnull以外の値を指定してinsert/updateした場合(ID=2,3,4)、列データ(col 3)に値が格納される。nullの場合(ID=5)は値が格納されない。
COL2列追加後にinsert/updateした行データには、COL2列と対応する列データ(col 3)とは別の列データ(col 2)が追加されている。
ALL_TAB_COLSで表定義を確認すると、"SYS_NC00003$"というシステム生成の非表示列が追加されている。
SQL> select
COLUMN_ID,
COLUMN_NAME,
HIDDEN_COLUMN,
USER_GENERATED
from
ALL_TAB_COLS
where
TABLE_NAME = 'ADD_COL_DEF_NULL_TBL'
order by COLUMN_ID;
COLUMN_ID COLUMN_NAME HIDDEN_COLUMN USER_GENERATED
---------- ------------ ------------- --------------
1 ID NO YES
2 COL1 NO YES
3 COL2 NO YES
SYS_NC00003$ YES NO参考：ALL_TAB_COLS
この非表示列(SYS_NC00003$)はCOL2列の値としてメタデータ(ecol$)に格納されているデフォルト値を返すのか、列データ(col 3)に格納されている値を返すのかを判断するフラグとなっている。
この非表示列の値がnull（ID=1）もしくは'0'であれば、COL2列のselect結果はデフォルト値を返す。非表示列の値が'1'であれば（ID=2,3,4,5）、列データ(col 3)に格納されている値を返す。
このフラグが無ければ、既存行に列を追加したためデータブロックに値が格納されていないのか（デフォルト値を返すケース）、追加列に対してnullを設定したため行データに値が格納されていないのか（nullを返すケース）、判断ができない。
以下のエントリにこのフラグを使った内部動作の詳細がまとめられている。
Ilmar Kerm DB blog - blog address has changed -: 12c metadata only default values for NULL columns
