今週の名言

「幸福になる秘訣は快楽を得ようとひたすらに努力することではなく、努力そのもののうちに快楽を見出すことである。」
アンドレ・ジット

マスタ/ディテール関係の実装を考える

今回は検証の前にデータ構造設計の話をしておきたいと思います。

売上－売上明細のように１対多のカーディナリティを持つ２つのテーブル（正確にはエンティティと表現すべきですが、物理的に実装するところまでを述べるのでテーブルと呼称しつつ、エンティティという表現も適宜使い分けることにします。）のリレーションシップをマスタ/ディテール関係と呼んだりすることがあります。

簡単に言うと「親子関係」であり、ER図（IE表記）で表現すると以下のようになります。（図１）

図１

• 親表（T_PAR）の主キー（PK）はPIDであり、子表（T_CHD）の主キーの一部を成すことで親子関係を表現することができる。
• この図では子表（T_CHD）の主キー（PK）は「PID + CSEQ（連番）」であり、ユニーク（一意）性を担保する必要がある。
• IE表記において、子表を角の丸い四角形で表現した場合、親表に対する「従属エンティティ」あるいは「依存エンティティ」であることを意味する。
• つまり、子表の主キーの一部であるPIDは必ず存在する必要がある。（Not Null制約）

この親子関係は、次のようにも表現することができます。（図２）

図２

• 子表（T_CHD）の主キー（PK）はCIDのみであり、このカラムだけで一意性を担保できる。
• 主キー項目以外に、子表には親表のPID（外部キー、FK）を持つ必要があり、かつこのカラムはNot Nullである必要がある。
• 子表を角がある四角形で表現した場合、「独立エンティティ」あるいは「非依存エンティティ」であることを意味する。
• つまり、PID列がNullであっても子レコードは存在できるが、前述のようにPID列は必ずNot Nullであるので論理的に従属関係にある。

今検証しているIdentity Columnを活用してマスタ/ディテール関係を実装しようとすると、必然的に図２の形になります。

従属的な親子関係を表現するためには必ずしも図１のような「従属エンティティ」を使う必要はないと思います。

子レコードが親レコードに依存しているということと、主キーの一部に親レコードの主キー値を持たなければならないというのは必ずしも同じでないということです。

重要なのは「親レコードを指す主キー値がNullであってはならない。」ということなのです。

親－子－孫関係を考える

図１、図２どちらでもよい、というのはある意味悩ましい事実です。

そこでこの問題をより掘り下げるために、親－子－孫の関係を考えてみましょう。

従属エンティティは先祖をたどることができる

図３

図３を見れば明らかですが、従属エンティティは孫エンティティから先祖である親エンティティを特定することができます。つまり、主キーに「PID」というカラムを持つテーブルは、すべて親エンティティに従属することがわかります。

例えばPID単位でパーティショニングを行うような場合、これは大きなメリットと言えるかもしれません。

ただし、主キーが複数のカラムで構成されるデメリットも当然あります。親エンティティの主キー構成が万一変更されるような場合、従属するすべてのエンティティに影響が及びます。

主キーは本来変更されない前提で設計されるのですが、未来永劫変更されない保証はありません。

また、設計の初期段階で親エンティティの主キー構成がなかなか決まらないと子エンティティの設計もなかなか決まらないという恐れもあります。（こちらの方が多いかもしれません。）

独立エンティティは構造がシンプル

図４

一方、独立エンティティは１つの主キー項目とそれ以外の項目というデータ構造は、親・子・孫で変わることはありません。
主キー項目は文字通りID（IDentity）項目であり、行を一意に識別することができます。そしてOracleデータベースの場合、シーケンス・オブジェクト（Sequence、順序）が生成する重複のない値を使用します。

この図をよく見ると、孫エンティティには親エンティティの主キーであるPIDを保持するカラムがありません。従って自分の先祖を簡単にたどることができません。

しかし、これを単純にデメリットと捉えることは間違いだと思います。実際にクエリを記述する場合、ジョイン（結合）するテーブルは２つだけですので、例えば子と孫テーブルをジョインする場合、その上の親テーブルを意識する必要はありません。ジョインの結果得られた結果セットと親テーブルをジョインするからです。

むしろ孫テーブルにPIDを持つと、親・孫という間違ったジョインを記述してしまう恐れがあります。そのような場合重複行が発生してしまい、無理矢理distinctで重複行を排除するようなことをしてしまうのです。これにより不要なFull Scanが発生するようなこととなり、ミスをミスで隠蔽するという最悪のSQL文を書いてしまっていたというケースをよく見かけます。

従属エンティティのメリットとしてパーティショニングがたやすい、ということを挙げましたが独立エンティティでも工夫次第ではパーティション化をすることができます。これは簡単な発想で解決できますので、是非考えてみてください。

「独立・依存」エンティティをIdentity Columnで実装する

独立した子エンティティでありながら外部キー項目をNot Nullとする設計を「独立・依存エンティティ」と呼びたいと思います。

一概に決め付けることは厳に慎むべきですが、設計時の混乱や運用中の性能問題を数多く見てきた経験から、この「独立・依存エンティティ」の考え方は有効な解決策となり得ると実感しています。

実際にデータ構造のリファクタリングを成功させた例では、この基本的な考え方に沿って設計を進めました。

ID列は複数のカラムからなる主キーとは違い、代替キーとかサロゲートキーと呼ばれます。何が何でもサロゲートキーにしなければならないと主張するつもりはありません。しかし、どんなキーが自然にユニークとなる識別キー（ナチュラルキー）になるかはよく考える必要があります。

私は、世の中の設計における混乱は、識別キーと検索キーを混同していることによることも多いと思います。

。。。

と前置きのつもりで書いていたら長くなってしまいましたので、「独立・依存エンティティ」を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の深いところを理解するには興味深いきっかけになるかもしれません。

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

続く

今週の名言

「人にものを教えることはできない。できることは、相手のなかにすでにある力を見いだすこと、その手助けである。」
ガリレオ・ガリレイ

今回は小ネタで

前回に引き続き「Identity Column」のつもりでしたが、事情により今回は別のトリビア的なネタを紹介します。

アラートログ監視の難しさ

Oracleの運用監視において、アラートログの中の「ORA-」で始まるメッセージを通知するということは当たり前に行われていると思いますが、エラー・メッセージ・マニュアルで「処置: 処置は必要ありません。」のように説明されているアラートが真夜中にエスカレーションされた経験はないでしょうか？

Oracleにある程度詳しい人であれば、経験から「これはとりあえず様子見でよいかな。」というような判断を下すことができるかもしれませんが、２４時間待機のオペレータにそこまでの判断を求めることは難しいのではないかと思います。

そこで、「このメッセージは監視対象から外す」というようなルールを決めているところは多いと思います。
私もそのような取り組みに関わったことがあるのですが、システムごとに発生するエラーの傾向が異なるので、どのシステムでも使える普遍的な「監視対象メッセージ一覧」を作るのは現実的にはかなり難しいのではないだろうかという実感を持っています。

ところでOracleエラーっていくつあるの？

いつまで経っても「監視対象メッセージ一覧」を作ることができないことに業を煮やし、Oracleエラーが一体いくつ存在しているのか気になって調べてみたことがあります。
エラー・メッセージ・マニュアルを頭から数えるのはかなり心が折れる作業になりそうなので、以下のPL/SQLスクリプトを作って調べてみました。

Oracle 11.2.0.4の場合

SQL> DECLARE
  2   sql_code NUMBER;
  3   str_pos  NUMBER;
  4   cnt      NUMBER;
  5  BEGIN
  6   str_pos := 0;
  7   cnt := 0;
  8   FOR sql_code IN 1..99999 LOOP
  9    str_pos := INSTR(SQLERRM(-sql_code),'not found;',1,1);
 10    IF str_pos = 0 THEN
 11     cnt := cnt + 1;
 12    END IF;
 13   END LOOP;
 14   DBMS_OUTPUT.PUT_LINE('Nuber of Error Code : ' || cnt);
 15  END;
 16  /
Nuber of Error Code : 54387

PL/SQL procedure successfully completed.

54,387個のメッセージという結果が得られました。

大訂正！！(2015.05.23)

Oracleエラーがこんなに多いはずがないとどうも腑に落ちなかったのですが、とんでもない間違いをしていたことに気付きました。

SQL> BEGIN
  2   DBMS_OUTPUT.PUT_LINE(SQLERRM(-99999));
  3  END;
  4  /
99999: non-ORACLE exception

PL/SQL procedure successfully completed.

この「non-ORACLE exception」をカウントに入れてました。orz
これでは純粋なORA-エラーになりません。

とりあえず修正版

この不要なメッセージも除外するようにコードを書き直して実行してみたのが以下の結果です。

SQL> DECLARE
  2   sql_code NUMBER;
  3   cnt      NUMBER :=0;
  4   err_msg  VARCHAR2(2000);
  5  BEGIN
  6  FOR sql_code IN 1..99999 LOOP
  7    err_msg := SQLERRM(-sql_code);
  8    IF err_msg NOT LIKE '%not found;%' AND err_msg NOT LIKE '%non-ORACLE exception%' THEN
  9     cnt := cnt + 1;
 10    END IF;
 11  END LOOP;
 12  DBMS_OUTPUT.PUT_LINE('Nuber of Error Code : ' || cnt);
 13  END;
 14  /
Nuber of Error Code : 19923

PL/SQL procedure successfully completed.

だいぶ、数が減りました。

実は、まだ無効なメッセージを含んでいる

ところが、以下のように番号は予約されているけれどメッセージが空欄のものが相当あることがわかりました。

SQL> BEGIN
  2   DBMS_OUTPUT.PUT_LINE(SQLERRM(-12428));
  3   DBMS_OUTPUT.PUT_LINE(SQLERRM(-12498));
  4   DBMS_OUTPUT.PUT_LINE(SQLERRM(-12499));
  5  END;
  6  /
ORA-12428:
ORA-12498:
ORA-12499:

PL/SQL procedure successfully completed.

こんなのも一緒にカウントしてたら正確なエラーの数はわかりませんね。

結局、SQLERRM関数を使って調査することは諦めることにします。

従って、上で挙げたエラー数は間違いですので無視してください。お騒がせして申し訳ありません。

その代わりもっとよい方法があるのでそちらを別途紹介することにします。

ORAエラーはいくつある？訂正しました。

今週の名言

「一時間の浪費を何とも思わない人は、まだ人生の価値を何も見つけていない。」
チャールズ・ダーウィン

前回のまとめ

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列に格納された情報から判断することができるようです。

次回はこの辺を詳しく見てみましょう。

続く