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

kagamihogeの日記

kagamihogeの日記です。

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

DB Oracle

整合性制約の有無によるINSERTの速度差 - kagamihogeのblogの続き。

整合性制約の有無でUPDATEに速度差が生じるかを確認する。検証する制約は、主キー・チェック・外部キー。制約を付与してからUPDATEするのと、UPDATEしてから制約を付与するのとで、UPDATEの実行時間を調べる。

準備

整合性制約の有無によるINSERTの速度差 - kagamihogeのblogで作ったempテーブルをコピーしておく。empテーブルには100万件入っている。

create table emp_bk as select empno, ename, deptno from emp order by empno;
alter table emp_bk add constraint pk_emp_bk_empno primary key("empno");

同一内容のUPDATEを三セット流し終わったあと、下記のようにしてテーブルを元に戻す。

drop table emp purge;
create table emp as select empno, ename, deptno from emp_bk order by empno;

検証するSQL

empテーブルに対して、UPDATE→ALTERとALTER→UPDATE、したときの実行時間を測定する。実行時間はUPDATEとALTERそれぞれで調べる。

UPDATEのパターンは三種類。主キー列であるempno、チェック制約のename、外部キー制約のdeptno、それぞれに対して行う。どのパターンでもwhere句はつけずに全件更新を行う。

empnoのケース。下記は制約を先に付与する場合。下記に示すように、alter,updateそれぞれの前でバッファキャッシュをクリアする。また、クリアに要した時間は実行時間の計測に含めない。クリアしたあと、alter,updateそれぞれを実行し、それぞれの実行時間を計測する。*1

alter system flush buffer_cache;--これの実行時間は含めない
alter table emp add constraint c_pk_emp_no primary key(empno);--これの実行時間を計測する。

alter system flush buffer_cache;--これの実行時間は含めない
update emp set empno = empno + 1;--これ+下のcommitまでの実行時間を計測する。
commit;

制約付与を後にする場合は、このようになる。

alter system flush buffer_cache;
update emp set empno = empno + 1;
commit;

alter system flush buffer_cache;
alter table emp add constraint c_pk_emp_no primary key(empno);

以下、制約付与の順番が異なるだけのものについてはSQLを省略する。また、バッファクリアのSQL文も記載を省略する。

enameのケース。

alter table emp add constraint c_ck_emp_ename_range
  check('0' < ename and ename < 'zzzzzzzzzz');

update emp set ename = substr(ename, 0, 5) || DBMS_RANDOM.STRING('a',5);
commit;

deptnoのケース。

alter table emp add constraint c_fk_emp_deptno
  foreign key(deptno)
  references dept(deptno);

update emp set deptno = deptno + 1;
commit;

計測結果

左側が、ALTER→UPDATE、右側が、UPDATE→ALTER、それぞれの実行結果。
これは非常に見辛いので書き直したものをこの一つ下に乗せている。alterの実行時間は、UPDATEの前でも後でも変化しない。結局100万件に対して実行することに変わりは無いので変化無いのは当然である。よって、結果表示からALTERの部分を省略し、UPDATEだけを比較する。

    1 2 3     1 2 3
empno alter 022.828 022.547 022.922 empno update 064.486 069.516 063.422
  update 090.172 088.109 086.328   alter 022.766 022.844 022.640
ename alter 001.906 001.938 001.812 ename update 117.437 118.454 110.437
  update 111.906 111.296 108.124   alter 001.829 001.875 001.875
deptno alter 010.937 011.000 010.766 deptno update 074.906 063.187 065.000
  update 045.469 045.500 044.234   alter 010.922 011.094 011.015

UPDATEの実行時間のみ抽出してヨコからタテに変更。制約欄の意味は、先はALTER→UPDATE, 後はUPDATE→ALTER

  制約 1 2 3
empno(主キー) 090.172 088.109 086.328
  064.486 069.516 063.422
ename(チェック) 111.906 111.296 108.124
  117.437 118.454 110.437
deptno(外部) 045.469 045.500 044.234
  074.906 063.187 065.000

感想とか

主キーについては、主キーインデックスを付けた状態でのUPDATEの方が遅いことが確認できた。インデックスのメンテナンスコストの有無でそうなっているのは明らか。

チェック制約については、僅かだが制約が存在した方が早くなっている。謎。

外部キー制約については更に謎で、外部キー制約が存在したほうがかなり早くなっている。実行計画にも差が無い。教授!これは一体!?

AUTOTRACEを取ってみる。

制約アリ

         44  recursive calls
    1021304  db block gets
       3389  consistent gets
       3455  physical reads
  240749876  redo size
        690  bytes sent via SQL*Net to client
        607  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

制約ナシ

        182  recursive calls
    1049826  db block gets
    1010557  consistent gets
      11817  physical reads
  307254608  redo size
        702  bytes sent via SQL*Net to client
        607  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

そりゃあ制約アリの方が速いっすね、という結果は分かる。制約アリのphysical readsがempテーブルのブロック数とほぼ等しいのは良いとして、ナシの方が4倍近いのが謎。consistent getsもすごいし、redo sizeの差も良く分からない。

非常に中途半端ではありますが。ひとまず、コレで終了。

*1:フツーはupdate直後にalterすると思われるので、update直後のバッファ残った状態でalterしたほうがより実態に近くなるとは思うのだけど、まぁいいや