Oracle 12c 連番を振る

引き続き、Oracle12cの新機能を試します。
今回は、連番を振るための2つの機能。

• Default Values for Columns Based on Oracle Sequences
• IDENTITY Columns

どちらも連番を振るための機能です。

これまで

Oracleで連番と言えばシーケンスオブジェクト。
こんな感じでした。

create sequence itemseq;

create table item (
  id number primary key
  , name varchar(20)
  , created timestamp default systimestamp
);

insert into item(id, name) values(itemseq.nextval, 'apple');
insert into item(id, name) values(itemseq.nextval, 'orange');
insert into item(id, name) values(itemseq.nextval, 'banana');

select id, name from item;

        ID NAME
---------- --------------------
         1 apple
         2 orange
         3 banana

ふつうはトリガー使うのかも。

Default Values for Columns Based on Oracle Sequences

カラムのデフォルト値にシーケンスオブジェクトの値を指定できるようになりました。
MySQLだと、auto_incrementというのをカラムにつけられてこれで自動採番できていたので、それと似たような感じ。

create sequence itemseq;

create table item (
  id number default itemseq.nextval primary key
  , name varchar(20)
  , created timestamp default systimestamp
);

insert into item(name) values('apple');
insert into item(name) values('orange');
insert into item(name) values('banana');

select id, name from item;

        ID NAME
---------- --------------------
         1 apple
         2 orange
         3 banana

一応カラム定義を見ておくと、こうなってます。

select column_name, data_default from cols where table_name='ITEM';

COLUMN_NAME                    DATA_DEFAULT
------------------------------ ------------------------------
ID                             "APP"."ITEMSEQ"."NEXTVAL"
NAME
CREATED                        systimestamp

IDENTITY Columns

ANSI標準で連番を振るための機能。
構文は下記に書いてある通り。
CREATE TABLE

create table item (
  id number generated always as identity primary key
  , name varchar(20)
  , created timestamp default systimestamp
);

insert into item(name) values('apple');
insert into item(name) values('orange');
insert into item(name) values('banana');

ALWAYSを指定しているので、値を入れようとするとエラーになります。

insert into item(id, name) values(4, 'grape')
                 *
行1でエラーが発生しました。:
ORA-32795: GENERATED ALWAYSで作成されたアイデンティティ列には挿入できません

ALWAYSではなBY DEFAULTだとデフォルト値扱いなので、別の値を設定することができます。

insert into item(id, name) values(5, 'banana');

select id,name from item;

        ID NAME
---------- --------------------
         1 apple
         2 orange
         3 banana
         5 banana

採番後にロールバックしても、シーケンスオブジェクトと同様にロールバックされません。

insert into item(name) values('apple');
insert into item(name) values('orange');
insert into item(name) values('banana');

select id,name from item;

        ID NAME
---------- --------------------
         1 apple
         2 orange
         3 banana

rollback;

insert into item(name) values('apple');
insert into item(name) values('orange');
insert into item(name) values('banana');

select id,name from item;

        ID NAME
---------- --------------------
         4 apple
         5 orange
         6 banana

テーブル定義上もIDENTITY COLUMNであることを確認できます。

select column_name, identity_column from cols where table_name='ITEM';

COLUMN_NAME                    IDE
------------------------------ ---
ID                             YES
NAME                           NO
CREATED                        NO

実際はこっそりシーケンスオブジェクトを作っているようです。
ロールバック時の振る舞いも一致してますね。
なので、シーケンスオブジェクト作成権限がないと、IDENTITY Columnsを定義できないようです。

select sequence_name from user_sequences;

SEQUENCE_NAME
--------------------------------------------------------------------------------
ISEQ$$_92058


select * from user_tab_identity_cols;

TABLE_NAME COLUMN_NAM GENERATION IDENTITY_OPTIONS
---------- ---------- ---------- ------------------------------
ITEM       ID         ALWAYS     START WITH: 1, INCREMENT BY: 1
                                 , MAX_VALUE: 99999999999999999
                                 99999999999, MIN_VALUE: 1, CYC
                                 LE_FLAG: N, CACHE_SIZE: 20, OR
                                 DER_FLAG: N

まとめ

採番方法が増えた。
今までみたいにシーケンスオブジェクト用意してトリガー張ったりといったことをしなくてもよくなった。
どの方法も結局シーケンスオブジェクトを使っているので、内部的には変わらないのだろう。
個人的には、IDENTITY Columnsが直感的で分かりやすいかな。
テーブルとシーケンスに別々に行っていたこと（expdmp/impdmpとか）をする時、テーブルだけを対象にしたとしても、隠れシーケンスオブジェクトもよろしくやってくれるのかは気になるところ。