今週の名言
「一時間の浪費を何とも思わない人は、まだ人生の価値を何も見つけていない。」
チャールズ・ダーウィン
前回のまとめ
12c新機能である「Identity Column」（日本語マニュアルでは「IDENTITY列」）の検証をしています。
前回はこの機能の基本的な動作を確認しました。
テーブル作成時にID列（主キー列）にIdentity Columnの設定を行えば、暗黙的にシーケンス・オブジェクト（順序ジェネレータ、以下順序）が作成されます。
そして、テーブルにレコードをインサートする場合、ID列にどのような値を設定するかを全く意識することがなく（つまりSQL文実行者としては結果的にNullを指定してインサート）ても、レコードを一意に識別できるシーケンス番号が自動的に払い出されインサートが行われます。（サロゲート・キーの生成）
これは、ANSI準拠の他RDBMS（SQL Server、DB2等）からの移行を容易にするものでもあります。
テーブルと順序は互いに独立したオブジェクトだけど
前回、Identity Columnを設定したテーブルを削除（Drop）しても順序は順序として機能することを確認しました。
また、順序の名称も「ISEQ$$_91795」のようにテーブル名とは全く関係のないものになっているので、例えばEMP表の順序であれば「SEQ_EMP」のような名称が自然だと思っていた私にはこの機能は「ちょっと使えないんじゃないか？」という第一印象がありました。
ところが、テーブルは削除しても実体はリサイクルビンといういわゆるゴミ箱で管理されていますが、このリサイクルビンを明示的にパージ（空に）したところ、該当する順序も自動的に削除されていたので、Identity Columnで作成された順序と元テーブルにはこれらを紐付ける何らかの仕組みがあるのではと気付きました。
実は、USER_TAB_IDENTITY_COLSというディクショナリ・ビューでこの関係性を確認することができるのですが、今回はSQLトレースを取得することでどのような内部表にこの情報が格納されているのかを探ってみたいと思います。
1. SQLトレースの準備
それでは、まずSQLトレースを取得する準備をします。トレースファイルはtraceディレクトリ以下に大量に出力されていますので、「IDNCOL」という識別子を付けて該当するファイルが簡単にわかるようにしておきます。
トレースファイルのフルパス名称はV$DIAG_INFOから確認することができます。
SQLトレースは、バインド変数とその内容を確認したいので、レベル４を指定します。
SQL> SHOW USER ユーザーは"TEST"です。 SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='IDNCOL'; セッションが変更されました。 SQL> SELECT NAME,VALUE FROM V$DIAG_INFO 2 WHERE NAME = 'Default Trace File'; NAME VALUE -------------------- --------------------------------------------------------------------------- Default Trace File /u01/app/oracle/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_31051_IDNCOL.trc SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'; セッションが変更されました。
2. Identity Column付きテーブルの作成（２種類）
前回ご紹介した要領でテーブルを作成しますが、「ALWAYS」と「BY DEFAULT ON NULL」でそれぞれT1表、T2表を作成します。
また、テーブル作成後にそれぞれ主キーを作成します。
SQL> CREATE TABLE t1 2 (c11 NUMBER GENERATED ALWAYS AS IDENTITY 3 ,c12 VARCHAR2(10) 4 ); 表が作成されました。 SQL> ALTER TABLE t1 ADD CONSTRAINT pk_t1 PRIMARY KEY (c11); 表が変更されました。 SQL> CREATE TABLE t2 2 (c21 NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY 3 ,c22 VARCHAR2(10) 4 ); 表が作成されました。 SQL> ALTER TABLE t2 ADD CONSTRAINT pk_t2 PRIMARY KEY (c21); 表が変更されました。 SQL> SELECT OBJECT_NAME,OBJECT_ID,OBJECT_TYPE 2 FROM USER_OBJECTS 3 ORDER BY OBJECT_ID; OBJECT_NAME OBJECT_ID OBJECT_TYPE ------------ ---------- ----------------------- T1 91832 TABLE ISEQ$$_91832 91833 SEQUENCE PK_T1 91834 INDEX T2 91835 TABLE ISEQ$$_91835 91836 SEQUENCE PK_T2 91837 INDEX 6行が選択されました。 SQL> SELECT SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER 2 FROM USER_SEQUENCES; SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER --------------- ------------ ---------- ----------- ISEQ$$_91832 1 20 1 ISEQ$$_91835 1 20 1
USER_OBJECTSを見てわかるように、順序の名称「ISEQ$$_91832」の数字部分は元表のオブジェクトIDです。
そして順序自身のオブジェクトIDは+1した「91833」となっています。
テーブルと順序の作成はCREATE TABLE時１トランザクションで実行されるので、OBJECT_IDは必ず続き番号になるようです。（ALTER TABLE時を除く）
このような仕組みが分かっていればテーブルと順序の関係性はすぐにわかるのですが、これがもっと便利にわかるのが「USER_TAB_IDENTITY_COLS」ビューです。
3. USER_TAB_IDENTITY_COLS
このビューの説明はALL_TAB_IDENTITY_COLSを参照してください。
SQL> SELECT TABLE_NAME,COLUMN_NAME,GENERATION_TYPE,SEQUENCE_NAME,IDENTITY_OPTIONS
2 FROM USER_TAB_IDENTITY_COLS
3 ORDER BY TABLE_NAME;
TABLE_NAME COLUMN_NAME GENERATION SEQUENCE_NAME IDENTITY_OPTIONS
----------- ------------ ---------- --------------- ----------------------------------------
T1 C11 ALWAYS ISEQ$$_91832 START WITH: 1, INCREMENT BY: 1, MAX_VALU
E: 9999999999999999999999999999, MIN_VAL
UE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, OR
DER_FLAG: N
T2 C21 BY DEFAULT ISEQ$$_91835 START WITH: 1, INCREMENT BY: 1, MAX_VALU
E: 9999999999999999999999999999, MIN_VAL
UE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, OR
DER_FLAG: N
このビューを確認すれば、TABLE_NAME列とSEQUENCE_NAME列からテーブルと順序の関係性を知ることができます。
GENERATION列からは「ALWAYS」「BY DEFAULT (ON NULL)」のオプションの違いを確認することができます。
さらに、IDENTITY_OPTIONS列によって順序定義の詳細がわかります。
4. テーブルの削除
次にT1表をパージ・オプション付きで、T2表をパージ・オプションを付けないで削除します。
SQL> DROP TABLE t1 PURGE;
表が削除されました。
SQL> DROP TABLE t2;
表が削除されました。
SQL> SELECT SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
2 FROM USER_SEQUENCES;
SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER
--------------- ------------ ---------- -----------
ISEQ$$_91835 1 20 1
SQL> SELECT TABLE_NAME,COLUMN_NAME,GENERATION_TYPE,SEQUENCE_NAME,IDENTITY_OPTIONS
2 FROM USER_TAB_IDENTITY_COLS
3 ORDER BY TABLE_NAME;
TABLE_NAME COLUMN_NAME GENERATION SEQUENCE_NAME IDENTITY_OPTIONS
----------- ------------ ---------- --------------- ----------------------------------------
BIN$Em/lpxe C21 BY DEFAULT ISEQ$$_91835 START WITH: 1, INCREMENT BY: 1, MAX_VALU
feUvgUwoLqM E: 9999999999999999999999999999, MIN_VAL
B1cw==$0 UE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, OR
DER_FLAG: N
パージ・オプションを付けないで削除した場合、テーブルは「BIN$Em/lpxefeUvgUwoLqMB1cw==$0」のように「BIN$」で始まる名前にRenameされます。
ただし、テーブルのデータを含めた実体はまだ存在していますので、順序との関係性が維持されているわけです。
5. SQLトレースの終了
以下の要領で、SQLトレースを終了します。さらにコマンドプロンプトからtkprofを実行し、トレースファイルを整形しておきます。
SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'; セッションが変更されました。 ----- $ cd /u01/app/oracle/diag/rdbms/ora12c/ora12c/trace $ tkprof ora12c_ora_31051_IDNCOL.trc IDNCOL.txt
6. USER_TAB_IDENTITY_COLSの定義を確認する。
SQLトレースの解析は次回にしますが、今回はそのためにUSER_TAB_IDENTITY_COLSの定義を予め確認しておくことにします。
SQL> SELECT TEXT FROM DBA_VIEWS WHERE VIEW_NAME = 'USER_TAB_IDENTITY_COLS';
TEXT
--------------------------------------------------------------------------------
select o.name, c.name,
decode(bitand(c.property, 137438953472 + 274877906944),
137438953472, 'ALWAYS',
274877906944, 'BY DEFAULT'),
so.name,
'START WITH: ' || i.startwith ||
', INCREMENT BY: ' || s.increment$ ||
', MAX_VALUE: ' || s.maxvalue ||
', MIN_VALUE: ' || s.minvalue ||
', CYCLE_FLAG: ' || decode (s.cycle#, 0, 'N', 1, 'Y') ||
', CACHE_SIZE: ' || s.cache ||
', ORDER_FLAG: ' || decode (s.order$, 0, 'N', 1, 'Y')
from sys.idnseq$ i, sys.obj$ o, sys.col$ c,
sys.seq$ s, sys.obj$ so
where o.owner# = userenv('SCHEMAID')
and o.obj# = i.obj#
and c.intcol# = i.intcol#
and c.obj# = i.obj#
and s.obj# = i.seqobj#
and so.obj# = i.seqobj#
;
12cから新しくできた「sys.idnseq$」表という実表が鍵を握っているようです。
また、「ALWAYS」「BY DEFAULT (ON NULL)」のオプションはsys.col$.property列に格納された情報から判断することができるようです。
次回はこの辺を詳しく見てみましょう。
続く