整合性制約の有無による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したほうがより実態に近くなるとは思うのだけど、まぁいいや
