読者です 読者をやめる 読者になる 読者になる

kagamihogeの日記

kagamihogeの日記です。

整合性制約の有無によるINSERTの速度差

DB Oracle

(略)次のような場合は、パフォーマンス上の理由から、表の整合性制約を一時的に使用禁止にすることを検討してください。
・表に大量のデータをロードする場合
・表に大規模な変更を加えるバッチ操作を実行する場合(たとえば、既存の番号に1000を加えてすべての従業員番号を変更する場合)
・1つの表を一度にインポートまたはエクスポートする場合
これら3つの場合には、整合性制約を一時的に使用禁止にすることにより、操作のパフォーマンスを改善できます。
Oracle Database管理者ガイド11g リリース2 (11.2) - 整合性制約の管理

Oracleのドキュメントには上記のように、大量のDML文を実行する場合は整合性制約を一時的に使用禁止状態することで、パフォーマンスを改善可能かもしれない、と書いてある。ので、これを実際に試してみることにする。

やることは、整合性制約なしでINSERTして制約を付与と、整合性制約ありでINSERTするのとで、速度差を見る。件数は100万件。使用禁止じゃなくて後から制約追加だけど、意味的には同じことだと思うのでその点はアマリ考えないことにする。

準備

データ挿入対象のテーブル。INSERT速度検証用のPL/SQLを実行するたびにdrop&createする。

drop table emp purge;
create table emp
(
  empno numeric(7),
  ename varchar(10),
  deptno numeric(6)
);

外部キー用のテーブル。empのdeptnoとdeptのdeptnoを関連付けするのに使用。

create table dept
(
  deptno numeric(6) not null,
  dname varchar(14),
  constraint dept_pk primary key 
  (
    deptno
  )
);

deptテーブルに4件レコードを追加しておく。

insert into dept(deptno, dname) values(0, '00001234567890');
insert into dept(deptno, dname) values(1, '11111234567890');
insert into dept(deptno, dname) values(2, '22221234567890');
insert into dept(deptno, dname) values(3, '33331234567890');
commit;

整合性制約を追加、下記のようなSQLを実行する。これら一つずつの有無で実行する。チェック制約は意味の無いことをやっているが、テスト用にテキトーな制約を作っただけなので気にしないで欲しい。

--NOT NULL
alter table emp
   modify (empno constraint c_nn_empno not null);

--外部キー
alter table emp add constraint c_fk_emp_deptno
  foreign key(deptno)
  references dept(deptno);

--チェック
alter table emp add constraint c_ck_emp_ename_range
  check('0' < ename and ename < 'zzzzzzzzzz');

--一意キー
alter table emp add constraint c_uq_emp_no unique(empno);

--主キー
alter table emp add constraint c_pk_emp_no primary key(empno);

チェック制約9個を1個のALTER文にして実行した場合(以降、check 9_1と表記)の速度比較もしている。

alter table emp add constraint c_ck_emp_1 check
(
  '0' < ename
  and ename < 'zzzzzzzzzz'
  and length(empno) > 0
  and length(ename) > 0
  and length(deptno) > 0
  and empno >= '0'
  and deptno >= '0'
  and to_char(empno) >= '0'
  and to_char(deptno) >= '0'
);

チェック制約9個を9個のALTER文にして実行した場合(以降、check 9_9と表記)の速度比較もしている。

alter table emp add constraint c_ck_emp_1 check('0' < ename);
alter table emp add constraint c_ck_emp_2 check(ename < 'zzzzzzzzzz');
alter table emp add constraint c_ck_emp_3 check(length(empno) > 0);
alter table emp add constraint c_ck_emp_4 check(length(ename) > 0);
alter table emp add constraint c_ck_emp_5 check(length(deptno) > 0);
alter table emp add constraint c_ck_emp_6 check(empno >= '0');
alter table emp add constraint c_ck_emp_7 check(deptno >= '0');
alter table emp add constraint c_ck_emp_8 check(to_char(empno) >= '0');
alter table emp add constraint c_ck_emp_9 check(to_char(deptno) >= '0');

100万件EMPテーブルにINSERTするPL/SQL。コミット間隔は1000。主キーとなるempnoには1から連番入れて、enameにはランダム文字列、deptnoには0〜3(最初は1はじまりだが)を順繰りに入れていく。

create or replace 
PROCEDURE INSERT_EMP AS 
BEGIN
  FOR I IN 1..1000000 LOOP
    INSERT INTO EMP(EMPNO, ENAME, DEPTNO) VALUES(I, (DBMS_RANDOM.STRING('a',10)), MOD(I,4));
    IF MOD(I, 1000) = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
END INSERT_EMP;

実行時間の計測

下記ブロックを実行したときのSQL Develperの時間表示で行う。整合性制約の付与は、下記ブロックの実行前と後に行い、そのときの速度差を計測する。また、実行後に整合性制約を付与するのに要した実行時間も計測する。なお、ねんのため実行前にバッファをクリアしている。

alter system flush buffer_cache;
BEGIN
  INSERT_EMP();
END;

計測結果

整合性制約なしでINSERTのPL/SQL実行したときの時間と、実行後に整合性制約を付与するのにかかった時間は下記の通り。

 (秒) 1 2 3
INSERT 172.968 172.313 171.953
not null 001.500 001.516 001.360
foreign key 002.110 002.625 002.063
check 001.235 001.297 001.100
check 9_1 005.641 005.907 005.032
check 9_9 012.984 010.156 010.234
unique 005.343 006.625 005.703
primary key 007.266 006.359 007.266

整合性制約を先に付与してからINSERTのPL/SQL実行したときの時間。整合性制約付与に要した時間は極小なので、下記の実行時間には含めていない。

 (秒) 1 2 3
not null 173.954 172.812 173.656
foreign key 182.547 181.609 181.969
foreign key2 187.750 186.204 185.828
check 190.141 186.281 186.062
check 9_1 193.516 193.453 192.812
check 9_9 193.625 193.218 194.125
unique 206.719 205.296 204.718
primary key 205.436 207.500 205.344

上記のforeign key2は、外部キー先のDEPTを10万件にして実行時間を計ったもの。

感想とか

どんな種類の制約であれ、あとで追加するほうが速い結果となった。INSERT一件ごとに逐一チェックするより、まとめてチェックした方が早い、ということなんだろう。とはいえ、最も速度差がある一意キーないし主キーで約30秒差。ソレナリの差が出てはいるが、この数値は環境によっても変わって来るんで、まぁ参考値って感じでしょう。

一意キー・主キーはインデックスのメンテナンスコストのおかげで時間食うのや、INSERTが終了してから制約貼ってインデックス作る方が効率的なのも当然。

個人的に意外だったのは、外部キー存在時の実行時間はさほど変わらなかったこと。約10秒差ほど。チェック内容がカンタンで、かつ、制約の数が少なければ、そこまでの速度劣化には繋がらないのだろうか? 参照先(dept)を4件から10万件にしても実行時間は変わらず。チェックに使う列にインデックスがあれば外部キーの存在チェックのコストは最小だろうから、時間が変わらないのは理解できる。

not nullは実行時間さほど変わらず。チェック制約がやや遅く、check 9_1のケースで約15秒差ほどになっている。not nullは、列値がnullかそうでないかをチェックするだけなので、そんなに重い処理にはならないってことなんだろう。チェック制約は、該当のALTER文を参照してほしいが、文字列の比較なので、負荷はやや重めとなるのだろう。

制約をタクサン付与した場合はどうなるか。チェック制約の数を増やしたcheck 9_1,check 9_9のケースを考える。まず、制約の数が増えると、あとで制約を付与する時間がそれだけ増える。この場合、9_1も9_9も意味的には同一なので単一の制約で済むため、むやみに制約を分割する価値は無さそうである。ただ、9_1と9_9でチェック制約を付与してからのINSERTの実行時間には変わりが無かった。ちと不思議。

全般的には、INSERT一件ごとのオーバーヘッドはソレナリにあり、その回避にはINSERT実行後に制約付与することが有効、と言える。ただまぁ、こんだけカンタンなテーブルに100万件程度では1分遅くなるかどうか程度である。どちらかといえば、DWH系で超大規模のデータロード時に考慮したほうが良い事項なのだと思う。