今週の名言
「自分がわずかなことしか知らないということを知るためには、多くのことを知る必要がある。」
モンテーニュ
Oracle 12c新機能について
今週からOracle 12cの新機能を検証します。
実はOracle 12c 発表されたと思ったらリリース１(R1)はもうターミナル・リリースになるようです。（Database In-Memoryなど大幅に機能拡張：Oracle Database 12cR1の最新パッチセット12.1.0.2がリリースされました）
最近のOracle RDBMSは、R1のうちはイノベーター、アーリーアダプターまでが採用する傾向にあって、R2以降本格的にアーリーマジョリティ他に普及していくような都市伝説（？）があるように思います。
私が関わっている案件もほとんどが11gR2で、本番環境で12cに移行するというものをあまり聞いたことはありません。エンドユーザも状況を見ているのではないかという印象を持っています。
従って、12c新機能ネタはまだ新鮮味がある気がしますので、筆者が興味を持った機能を今後積極的に紹介していきたいと思います。
「Identity Column」とは？
12c新機能というと、プラガブル・データベースやDatabase In-Memoryなどが注目されていますが、このブログではどちらかというとあまり陽の当たらない機能で面白そうなものを紹介していこうと思います。
それも、単に機能を紹介するのではなく、実際に使う立場で検証してみたいと思います。
さて「Identity Column」ですが、ANSI準拠のIDENTITY Columnを実装したもので、平たく言うとInsert文における自動生成キー取り出し機能のことです。
マニュアルは以下を参照してください。
Oracle® Database新機能ガイド
12cリリース1 (12.1)
B71327-03
2.1.6.3 IDENTITY列
この機能は上記マニュアルの記述に先行した
- 2.1.6.1 Oracleの順序に基づく列のデフォルト値
- 2.1.6.2 明示的なNULL挿入での列のDEFAULT値
の組み合わせで使用する機能であるとも言えます。
「百聞は一見に如かず」とりあえず実際のDDL文から確認していきましょう。
基本動作の確認
検証環境
検証環境はOracle Linux 6.5とOracle12R1です。
SQL> !uname -r
3.8.13-16.2.1.el6uek.x86_64
SQL> select BANNER from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
CREATE TABLE文
検証用のTESTユーザはすでに作成済みです。CREATE SEQUENCE権限が付与されていることが必要です。
SQL> show user
ユーザーは"TEST"です。
SQL> create table t1
2 (c1 number GENERATED BY DEFAULT ON NULL AS IDENTITY
3 ,c2 varchar2(10)
4 );
表が作成されました。
「GENERATED 〜 AS IDENTITY」がIDENTITY句と呼ばれる記述ですが、「BY DEFAULT ON NULL」はオプションです。これを省略するとデフォルトの「ALWAYS」となります。（先で機能を説明するため、あえてこの設定にします。）
シーケンス確認
IDENTITY句を指定すると、裏で自動的にシーケンス・オブジェクト（順序）が作られます。
USER_SEQUENCEからその状況を確認しておきましょう。
SQL> select SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
2 ,CYCLE_FLAG,ORDER_FLAG
3 from user_sequences;
SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER C O
------------------------- ------------ ---------- ----------- - -
ISEQ$$_91795 1 20 1 N N
ISEQ$$_91795というシステムが命名した順序が作成されているのがわかります。
キャッシュ・サイズはデフォルトの「20」です。
１行インサートする。
それでは、まずは１行インサートしてみましょう。C1列には何も指定せずC2列のみ値を設定してインサートします。
さらに、直後の順序の状況を確認します。
SQL> insert into t1(c2) values ('abc');
1行が作成されました。
SQL> select SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
2 from user_sequences;
SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------- ------------ ---------- -----------
ISEQ$$_91795 1 20 21
LAST_NUMBERが 1+20(CACHE_SIZE)となっています。つまり最初の生成で1〜20までの数字がキャッシュされ、このインサートでは「1」が払い出されます。（次の生成では21〜40までがキャッシュされる予定です。）
もう１行インサートする。
さらにもう１行インサートします。
SQL> insert into t1(c2) values ('xyz');
1行が作成されました。
SQL> select c1,c2 from t1;
C1 C2
---------- ----------
1 abc
2 xyz
SQL> select SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
2 from user_sequences;
SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------- ------------ ---------- -----------
ISEQ$$_91795 1 20 21
２行作成されていることがわかります。キャッシュされている番号から払い出されているため順序の内容は変化していません。
一度ロールバックし、再度２行インサートする。
２行インサートした状態でコミットせずにロールバックすると、０行となります。
ここで、先ほど行った２行のインサートを行いC1列にどのような値が入るか確認します。
さらに今度はコミットします。
SQL> rollback;
ロールバックが完了しました。
SQL> select SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
2 from user_sequences;
SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------- ------------ ---------- -----------
ISEQ$$_91795 1 20 21
SQL> insert into t1(c2) values ('abc');
1行が作成されました。
SQL> insert into t1(c2) values ('xyz');
1行が作成されました。
SQL> select c1,c2 from t1;
C1 C2
---------- ----------
3 abc
4 xyz
SQL> commit;
コミットが完了しました。
テーブルはロールバックされましたが、順序「ISEQ$$_91795」には何の影響も与えません。
キャッシュされている次の番号「3」と「4」が続けて払い出されているのがわかります。
テーブルをドロップして順序の状況を確認する。
SQL> drop table t1;
表が削除されました。
SQL> select SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
2 from user_sequences;
SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------- ------------ ---------- -----------
ISEQ$$_91795 1 20 21
SQL> select ISEQ$$_91795.nextval from dual;
NEXTVAL
----------
5
テーブルを削除しても、順序はそのまま残っていることがわかります。
しかも、キャッシュされているシーケンス番号から次の番号が何の問題もなく払い出せることが確認できます。
リサイクルビンのパージ
テーブルを削除してもリサイクルビンで管理されるだけなので、念のためリサイクルビンを空にして順序の状況を確認します。
SQL> purge recyclebin;
リサイクルビンがパージされました。
SQL> select SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
2 from user_sequences;
レコードが選択されませんでした。
リサイクルビンを空にして、つまり本当にテーブルを削除すると、テーブル作成時に自動的に作成された順序も削除されるようです。
「BY DEFAULT ON NULL」の意味を確認する。
「IDENTITY Column」の特徴は、値を指定しないで（つまりNullで）インサートしても自動的に連番が設定されることができるというものですが、明示的に値を設定することもできるいうのが「BY DEFAULT ON NULL」オプションです。
結論から言うと、どちらでもよいというのは意味がなくむしろ別の問題の原因となるのでこのオプションを指定すべきではないと考えます。
デフォルトの「ALWAYS」を指定すべきでしょう。
以下に、その理由を説明する検証を行っていきます。
キャッシュサイズを最小にする。
順序のキャッシュサイズをデフォルトの20にしておくと、次に払い出されるシーケンス番号が見えにくくなるので、キャッシュサイズを最小にしてみます。（パフォーマンス上の理由からキャッシュサイズはデフォルトの20より小さい値にすべきではありませんが、検証上あえて小さくします。）
SQL> create table t2
2 (c1 number GENERATED BY DEFAULT ON NULL AS IDENTITY (CACHE 1)
3 ,c2 varchar2(10)
4 );
create table t2
*
行1でエラーが発生しました。:
ORA-04010: CACHEには1より大きい値を指定する必要があります
おっと！「CACHE 1」という設定は許されていないようです。
それでは「NOCACHE」でやり直します。
SQL> create table t2
2 (c1 number GENERATED BY DEFAULT ON NULL AS IDENTITY (NOCACHE)
3 ,c2 varchar2(10)
4 );
表が作成されました。
SQL> select SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
2 from user_sequences;
SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------- ------------ ---------- -----------
ISEQ$$_91798 1 0 1
今度はうまくいきました。
C1列に主キーを設定する。
C1列は本来であればID列であるので主キーを設定します。
SQL> alter table t2 add constraint pk_t2 primary key (c1);
表が変更されました。
C1列に値を指定せずにインサートする
C1列は順序が設定されている列なので、値を指定せずにインサートするのが通常のインサートです。
とりあえず最初のレコードをインサートします。
SQL> insert into t2(c2) values ('abc');
1行が作成されました。
SQL> select SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
2 from user_sequences;
SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------- ------------ ---------- -----------
ISEQ$$_91798 1 0 2
SQL> select c1,c2 from t2;
C1 C2
---------- ----------
1 abc
C1列に値を指定してインサートする。
今度は、明示的にC1列に値を指定してインサートしてみます。
SQL> insert into t2(c1,c2) values (2,'xyz');
1行が作成されました。
SQL> select SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
2 from user_sequences;
SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------- ------------ ---------- -----------
ISEQ$$_91798 1 0 2
特に問題なくインサートできます。
（ALWAYSの場合はここでエラーとなります。）
また、LAST_NUMBERが「2」のままであることも注目すべきです。つまり次に払い出されるシーケンス番号は「2」です。
今度はC1列に値を指定しないでインサートする。
SQL> insert into t2(c2) values ('xyz');
insert into t2(c2) values ('xyz')
*
行1でエラーが発生しました。:
ORA-00001: 一意制約(TEST.PK_T2)に反しています
SQL> select c1,c2 from t2;
C1 C2
---------- ----------
1 abc
2 xyz
主キーの重複エラーが発生します。
なぜなら、既に「C1=2」となるレコードをインサートしているので、順序が払い出した「2」が重複してしてしまいエラーとなりました。
もう一度C1列に値を指定しないでインサートする。
ORA-00001エラーが発生するとそのレコードはインサートされませんが、もう一度値を指定しないでインサートするレコードはどうなるでしょうか？
SQL> insert into t2(c2) values ('def');
1行が作成されました。
SQL> select c1,c2 from t2;
C1 C2
---------- ----------
1 abc
2 xyz
3 def
SQL> select SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
2 from user_sequences;
SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------- ------------ ---------- -----------
ISEQ$$_91798 1 0 4
今度は、次に払い出されるシーケンス番号が「3」となり重複しないので、エラーは発生しません。
結論：IDENTITY列は「GENERATED ALWAYS AS IDENTITY」で設定すべき。
「Identity Column」のメリットあるいは注意点に関する考察は次回以降に回したいと思いますが、今回確認した中での結論はID列に値を指定する選択を許す「BY DEFAULT ON NULL」オプションは使うべきではないということです。
言い換えると「BY DEFAULT ON NULL」とは、値を指定しなければ（Null）順序から番号を払い出し、指定すればその値でインサートするという仕様と考えることもできます。
値を指定するとエラーとなるデフォルトの「ALWAYS」を使うべきだと思います。
create table t1
(c1 number GENERATED ALWAYS AS IDENTITY -- (ALWAYS)は省略可
,c2 varchar2(10)
);
続く
