12c新機能「Identity Column」の検証③

今週の名言

「事を行うにあたって、いつから始めようかなどと考えているときには、すでに遅れをとっているのだ。」
クインティリアヌス

前々回のおさらい

前回は別のテーマだったので、前々回の内容をおさらいしておきます。

  1. SQLトレースの設定
    • テーブル作成時に内部的にどのようなSQLが実行されているかを確かめるためにSQLトレースを事前に設定しておきます。
  2. Identity Column付きテーブルの作成（２種類）
    • 「ALWAYS」と「BY DEFAULT ON NULL」それぞれのオプションでテーブルを作成します。
  3. USER_TAB_IDENTITY_COLSの確認
    • TABLE_NAME列とSEQUENCE_NAME列からテーブルと順序の関係を確認します。
  4. テーブルの削除
    • ２つのテーブルのうち１つをパージ・オプション付き、もう片方をパージ・オプションなしで削除します。
  5. SQLトレースの終了
    • SQLトレースの取得を終了し、1 〜 5 の間に実行されたSQLの情報を確認する準備を行います。
  6. USER_TAB_IDENTITY_COLSの定義を確認する。
    • SQLトレースの確認を行うため、ビューを構成する実表を予め確かめておきます。
    • 「sys.idnseq$」表および「sys.col$」表が調査対象であることを確認しました。

sys.idnseq$

SQLトレースを直接確認する前にtkprofで整形した結果から調査対象の実表を検索します。

tkprofの結果から

まず、Identity Columnの実装で最も重要と思われる sys.idnseq$表を探すと以下のInsert文が見つかりました。

SQL ID: cdzktrqhtspgg Plan Hash: 0

insert into idnseq$(obj#, intcol#, seqobj#, startwith)
values
 (:1, :2, :3, :4)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          2          6           2
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          2          6           2

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 2

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD TABLE CONVENTIONAL  IDNSEQ$ (cr=1 pr=0 pw=0 time=125 us)

２つのテーブルを作成したので、Parse, Executeともに２回ずつ実行されています。
また、バインド変数が４つあることがわかります。

次にSQLトレースを直接参照してバインド変数の内容を確認します。

SQLトレースの内容

tkprofの結果から確認した SQLID=cdzktrqhtspgg によって、SQLトレースの中を探します。
先ほど確認したInsert文を含む以下の部分が見つかりました。

PARSING IN CURSOR #140330607060416 len=78 dep=1 uid=0 oct=2 lid=0 tim=118633564085 hv=2711377391
 ad='722200c8' sqlid='cdzktrqhtspgg'
insert into idnseq$(obj#, intcol#, seqobj#, startwith) values (:1, :2, :3, :4)
END OF STMT
PARSE #140330607060416:c=1000,e=220,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=118633564084
BINDS #140330607060416:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=72 off=0
  kxsbbbfp=7fa143f98fb8  bln=22  avl=04  flg=05
  value=91832
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=7fa143f98fd0  bln=22  avl=02  flg=01
  value=1
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=48
  kxsbbbfp=7fa143f98fe8  bln=22  avl=04  flg=01
  value=91833
 Bind#3
  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7523f68e  bln=22  avl=02  flg=09
  value=1
EXEC #140330607060416:c=0,e=634,p=0,cr=1,cu=3,mis=1,r=1,dep=1,og=4,plh=0,tim=118633564764
STAT #140330607060416 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  IDNSEQ$ (cr=1 pr=0 pw=0 time=160 us)'
CLOSE #140330607060416:c=0,e=3,dep=1,type=0,tim=118633564815

OBJID=91832がobj#に、OBJID=91833がseqobj#にそれぞれ代入されていることがわかります。

これにより、テーブルと順序の紐付けができています。

Delete文を探す

次に、テーブルをパージ・オプション付きで削除した際に発行される、Idenitty Column に関するDelete文を探してみましょう。

PARSING IN CURSOR #140330605974448 len=32 dep=1 uid=0 oct=7 lid=0 tim=118957259659 hv=933734747 ad='724c30b8' sqlid='59vjj34vugaav'
delete from obj$ where obj# = :1
END OF STMT
PARSE #140330605974448:c=0,e=556,p=0,cr=0,cu=2,mis=1,r=0,dep=1,og=4,plh=0,tim=118957259659
BINDS #140330605974448:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fa143d49440  bln=22  avl=04  flg=05
  value=91833
EXEC #140330605974448:c=2000,e=1700,p=0,cr=2,cu=11,mis=1,r=1,dep=1,og=4,plh=225269600,tim=118957261436
STAT #140330605974448 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE  OBJ$ (cr=2 pr=0 pw=0 time=410 us)'
STAT #140330605974448 id=2 cnt=1 pid=1 pos=1 obj=36 op='INDEX RANGE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=37 us cost=2 size=103 card=1)'
CLOSE #140330605974448:c=0,e=2,dep=1,type=3,tim=118957261516
FETCH #140330606193888:c=0,e=10,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=2429035722,tim=118957261705
STAT #140330606193888 id=1 cnt=1 pid=0 pos=1 obj=106 op='TABLE ACCESS BY INDEX ROWID BATCHED IDNSEQ$ (cr=3 pr=0 pw=0 time=13 us cost=0 size=26 card=1)'
STAT #140330606193888 id=2 cnt=1 pid=1 pos=1 obj=107 op='INDEX RANGE SCAN I_IDNSEQ1 (cr=2 pr=0 pw=0 time=14 us cost=0 size=0 card=1)'
CLOSE #140330606193888:c=0,e=12,dep=1,type=1,tim=118957261795
=====================
PARSING IN CURSOR #140330606969696 len=39 dep=1 uid=0 oct=7 lid=0 tim=118957261833 hv=1857173601 ad='74087130' sqlid='7h2gtvxrb4f31'
delete from sys.idnseq$ where obj# = :1
END OF STMT
PARSE #140330606969696:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3651899373,tim=118957261832
BINDS #140330606969696:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fa1445eea30  bln=22  avl=04  flg=05
  value=91832
EXEC #140330606969696:c=0,e=141,p=0,cr=1,cu=3,mis=0,r=1,dep=1,og=4,plh=3651899373,tim=118957262014
STAT #140330606969696 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE  IDNSEQ$ (cr=1 pr=0 pw=0 time=83 us)'
STAT #140330606969696 id=2 cnt=1 pid=1 pos=1 obj=107 op='INDEX RANGE SCAN I_IDNSEQ1 (cr=1 pr=0 pw=0 time=11 us cost=0 size=26 card=1)'
CLOSE #140330606969696:c=0,e=9,dep=1,type=1,tim=118957262089

最初に、OBJID=91833である順序がobj$表から削除され、OBJID=91832のエントリがsys.idnseq$表から削除され、Identity Columnの定義が削除されたことがわかります。

オプションはどのように管理されているのか？

USER_TAB_IDENTITY_COLSの定義の中で、decodeとbitand関数を使った部分があります。（以下のSQL文の4-6行目）
この部分はオプションを管理している部分です。ポイントは sys.obj$表のPROPERTY列にあります。

わかりやすいように、PROPERTY列を並べてどのような値が格納されているかを確認してみました。

SQL> select
  2    o.name TABLE_NAME
  3  , c.name COLUMN_NAME
  4  , decode( bitand(c.property, 137438953472 + 274877906944)
  5          , 137438953472, 'ALWAYS'
  6          , 274877906944, 'BY DEFAULT') GENERATION
  7  , c.property
  8  , so.name SEQUENCE_NAME
  9  from
 10    sys.idnseq$ i
 11  , sys.obj$ o
 12  , sys.col$ c
 13  , sys.seq$ s
 14  , sys.obj$ so
 15  where o.owner# = userenv('SCHEMAID')
 16  and o.obj# = i.obj#
 17  and c.intcol# = i.intcol#
 18  and c.obj# = i.obj#
 19  and s.obj# = i.seqobj#
 20  and so.obj# = i.seqobj#;

TABLE_NAME  COLUMN_NAME  GENERATION      PROPERTY SEQUENCE_NAME
----------- ------------ ---------- ------------- ---------------
T1          C11          ALWAYS      171798691840 ISEQ$$_91838
T2          C21          BY DEFAULT  377957122048 ISEQ$$_91840

以下は、PROPERYT列の十進数を二進数に変換してbitand演算を行ったところを説明したものです。

123456789012345678901234567890123456789
---------+---------+---------+---------
010100000000000000000000000000000000000 171798691840
110000000000000000000000000000000000000 137438953472 + 27487790694
---------------------------------------
010000000000000000000000000000000000000 137438953472 : 'ALWAYS'
 *

101100000000000000000000000000000000000 377957122048
110000000000000000000000000000000000000 137438953472 + 27487790694
---------------------------------------
100000000000000000000000000000000000000 274877906944 : 'BY DEFAULT'
*

この例では39bitのbit列における上位2bit目に1が立っていれば「ALWAYS」、1bit目に1が立っていれば「BY DEFAULT」となっていることがわかります。

データディクショナリ・ビューの定義を確認すると、このようにプロパティ列にビットフラグを立てて属性を管理しているのをよく見かけます。

あまり実業務には役に立たない雑学ですが、Oracleの深いところを理解するには興味深いきっかけになるかもしれません。

次回は、もう少し検証っぽいことをしてみようと思います。

続く