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

今週の名言

「無知を恐れてはいけない。偽りの知識を恐れよ。」
ブレーズ・パスカル

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

独立・依存エンティティそれでは、前回紹介した「独立・依存エンティティ(テーブル)」をIdentity Columnで実装してみましょう。
まずは、親表であるT_PAR表から作成します。Identity Columnのオプションは「ALWAYS」です。
忘れずに主キーも作成します。

SQL> CREATE TABLE t_par
  2  (pid  NUMBER GENERATED ALWAYS AS IDENTITY
  3  ,pval VARCHAR2(10)
  4  );

表が作成されました。

SQL> ALTER TABLE t_par ADD CONSTRAINT pk_t_par PRIMARY KEY (pid);

表が変更されました。

次に子表となるT_CHD表を作成します。親表とほぼ同じですが、PID列に参照整合性制約(外部キー)を作成するのがポイントです。

SQL> CREATE TABLE t_chd
  2  (cid  NUMBER GENERATED ALWAYS AS IDENTITY
  3  ,pid  NUMBER NOT NULL
  4  ,cval VARCHAR2(10)
  5  );

表が作成されました。

SQL> ALTER TABLE t_chd ADD CONSTRAINT pk_t_chd PRIMARY KEY (cid);

表が変更されました。

SQL> ALTER TABLE t_chd ADD CONSTRAINT fk_t_pid FOREIGN KEY (pid)
  2                        REFERENCES t_par(pid);

表が変更されました。

このタイミングで、表とともに作成された順序の名前も確認しておきましょう。

SQL> SELECT TABLE_NAME,COLUMN_NAME,SEQUENCE_NAME
  2  FROM USER_TAB_IDENTITY_COLS
  3  ORDER BY TABLE_NAME;

TABLE_NAME      COLUMN_NAME     SEQUENCE_NAME
--------------- --------------- ---------------
T_CHD           CID             ISEQ$$_91944
T_PAR           PID             ISEQ$$_91941

レコード作成スクリプト

連続したレコードを作成する以下のPL/SQLスクリプトを実行します。
ハイライト行に示すように、上で確認した順序名を直接指定しています。

SQL> get ins.sql
  1  DECLARE
  2   i NUMBER;
  3   j NUMBER;
  4  BEGIN
  5   FOR i IN 1..5 LOOP
  6    INSERT INTO t_par(pval) VALUES ('Parent');
  7    FOR j IN 1..5 LOOP
  8     INSERT INTO t_chd(pid,cval) VALUES (ISEQ$$_91941.currval,'Child');
  9    END LOOP;
 10    COMMIT;
 11   END LOOP;
 12* END;
SQL> @ins.sql

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT * FROM t_par;

       PID PVAL
---------- ----------
         1 Parent
         2 Parent
         3 Parent
         4 Parent
         5 Parent

SQL> SELECT * FROM t_chd;

       CID        PID CVAL
---------- ---------- ----------
         1          1 Child
         2          1 Child
         3          1 Child
................................
        23          5 Child
        24          5 Child
        25          5 Child

25行が選択されました。

親レコード1行毎に子レコードが5行作成され、全部で親レコード5行、子レコード25行が作成されていることがわかります。

共有プールをフラッシュさせてみる

順序が生成したシーケンス番号は、共有プール上に「CACHE_SIZE」パラメータで指定された数だけキャッシュされています。
デフォルト値は20となっており、次に生成されるシーケンス番号は以下のSQL文の「LAST_NUMBER」列で確認することができます。

SQL> SELECT SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
  2  FROM USER_SEQUENCES;

SEQUENCE_NAME   INCREMENT_BY CACHE_SIZE LAST_NUMBER
--------------- ------------ ---------- -----------
ISEQ$$_91941               1         20          21
ISEQ$$_91944               1         20          41

ここで、共有プールをフラッシュさせるとどのようになるかを確認します。

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

システムが変更されました。

再度、レコード作成スクリプトを実行し新たな25行をInsertします。

SQL> @ins.sql

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT * FROM t_par;

       PID PVAL
---------- ----------
         1 Parent
         2 Parent
         3 Parent
         4 Parent
         5 Parent
        21 Parent
        22 Parent
        23 Parent
        24 Parent
        25 Parent

10行が選択されました。

SQL> SELECT * FROM t_chd;

       CID        PID CVAL
---------- ---------- ----------
         1          1 Child
         2          1 Child
         3          1 Child
................................
        23          5 Child
        24          5 Child
        25          5 Child
        41         21 Child
        42         21 Child
        43         21 Child
................................
        63         25 Child
        64         25 Child
        65         25 Child

50行が選択されました。

上で確認した「LAST_NUMBER」列の値にシーケンス番号が飛んで採番されていることがわかります。(PID=21,CID=41)
飛び番は発生しますが重複が発生しているわけではないので、問題なくInsertできます。

レコード作成スクリプト改

「ISEQ$$_91941」のようにシステムが生成した順序の名前は、プログラムの中で使用するにはあまり適切ではありません。
できれば以下のハイライト行のように、開発者が認識できる順序の名前を使用できることが望ましいと考えます。

SQL> get ins1.sql
  1  DECLARE
  2   i NUMBER;
  3   j NUMBER;
  4  BEGIN
  5   FOR i IN 1..5 LOOP
  6    INSERT INTO t_par(pval) VALUES ('Parent');
  7    FOR j IN 1..5 LOOP
  8     INSERT INTO t_chd(pid,cval) VALUES (SEQ_PAR.currval,'Child');
  9    END LOOP;
 10    COMMIT;
 11   END LOOP;
 12* END;

順序は改名できない

テーブル名は「ALTER TABLE <テーブル名> RENAME <新テーブル名>」で改名することができます。
しかし、残念ながら順序は「ALTER SEQUENCE」文で名称を変更することはできません

改名はできませんが、シノニムを作成することで「別名」を使用することができます。
順序名を再確認し、早速シノニムを作成してみましょう。

SQL> SELECT SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
  2  FROM USER_SEQUENCES;

SEQUENCE_NAME   INCREMENT_BY CACHE_SIZE LAST_NUMBER
--------------- ------------ ---------- -----------
ISEQ$$_91941               1         20          41
ISEQ$$_91944               1         20          81

SQL> CREATE SYNONYM seq_par FOR ISEQ$$_91941;

シノニムが作成されました。

レコード作成スクリプト改を実行する

それでは、順序名をシノニムに置き換えたスクリプトを実行してみます。

SQL> @ins1.sql

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT * FROM t_par;

       PID PVAL
---------- ----------
         1 Parent
         2 Parent
         3 Parent
         4 Parent
         5 Parent
        21 Parent
        22 Parent
        23 Parent
        24 Parent
        25 Parent
        26 Parent
        27 Parent
        28 Parent
        29 Parent
        30 Parent

15行が選択されました。

SQL> SELECT * FROM t_chd;

       CID        PID CVAL
---------- ---------- ----------
         1          1 Child
         2          1 Child
         3          1 Child
................................
        63         25 Child
        64         25 Child
        65         25 Child
        66         26 Child
        67         26 Child
        68         26 Child
................................
        88         30 Child
        89         30 Child
        90         30 Child

75行が選択されました。

問題なく、新規レコードが作成されました。

もし、Identity Columnを使用しなければ

Oracle12c以前の環境で、同様のレコード作成スクリプトを書くと以下のようになります。
ハイライト行を冗長と見るか否かがIdentity Columnを使う判断基準となるのではないでしょうか?

SQL> get ins2.sql
  1  DECLARE
  2   i NUMBER;
  3   j NUMBER;
  4  BEGIN
  5   FOR i IN 1..5 LOOP
  6    INSERT INTO t_par(pid,pval) VALUES (SEQ_PAR.nextval,'Parent');
  7    FOR j IN 1..5 LOOP
  8     INSERT INTO t_chd(cid,pid,cval) VALUES (SEQ_CHD.nextval,SEQ_PAR.currval,'Child');
  9    END LOOP;
 10    COMMIT;
 11   END LOOP;
 12* END;

Identity Columnは使える機能か?

私がIdentity Columnという新機能を知った時、今回検証した親子関係のテーブルにおいて問題なく使用できるのであれば、プログラムを簡単にすることで開発効率品質を向上させることができる注目すべき機能である予感がしました。

上で確認したように、一旦テーブルを作成して順序名を確認してからシノニム(別名)を作成するという、ちょっと面倒な手順を踏めば何とか使えそうな気がしますが、このままでは今一な感じもします。

せめて、以下のようにテーブル作成時に任意の順序名を指定できる仕様であればよいのですが。。。

SQL> CREATE TABLE t_par
  2  (pid  NUMBER GENERATED ALWAYS AS IDENTITY "SEQ_PAR"
  3  ,pval VARCHAR2(10)
  4  );

Identity Columnは、現時点では他RDBMSからの移行を容易にするために実装された機能だと認識しています。

しかし、将来的により使いやすいように改良されることを期待しつつ、このシリーズを終えたいと思います。

終わり