外部キー制約にはインデックスを
今回は外部キー制約について。 テーブルのレコードの親子関係を正しく保つために外部キー制約を貼ることがあると思うが、その際は基本的にはインデックスを貼りましょうというお話。
概要
オラクルでは、主キー制約や一意制約の場合は作成時に自動的にそのインデックスが貼られるが 外部キー制約の場合は作成時に自動的にはインデックスは貼られない。 さて、このような挙動の違いから、外部キー制約を付与した際にインデックスを貼るべきか否かという疑問が湧いたので調べた結果を報告する。結論を先に述べると、基本的に貼るべきである。
なお、今回も以下の本を参考にした。
環境
- Linux : Red Hat Enterprise Linux Server release 6.5 (Santiago)
- oracle 12c ( Single Instance )
下準備
まずは親子関係を持つテーブルを用意する。
-- 親テーブルの作成
create table testuser.dept ( dept_id number, dept_name varchar2(20), constraint pk_dept primary key ( dept_id ) );
-- 子テーブルの作成
create table testuser.emp ( emp_id number, dept_id number, emp_name varchar2(30), constraint pk_emp primary key ( emp_id ) );
-- 外部キー制約の作成。親表のレコード削除時に子表のレコードも削除するオプションも付与している
alter table testuser.emp add constraint fk_emp foreign key ( dept_id ) references testuser.dept ( dept_id ) on delete cascade;
-- 親テーブルへレコードを挿入
insert into testuser.dept values ( 1, 'dept1' );
insert into testuser.dept values ( 2, 'dept2' );
insert into testuser.dept values ( 3, 'dept3' );
commit;
-- 子テーブルへレコードを挿入
insert into testuser.emp values ( 1, 1, 'Ichiro' );
insert into testuser.emp values ( 2, 1, 'Hanako' );
insert into testuser.emp values ( 3, 1, 'Jiro' );
insert into testuser.emp values ( 4, 2, 'Yojiro' );
insert into testuser.emp values ( 5, 3, 'Emily' );
commit;
インデックスの有無で変わる挙動
外部キー制約はあるがインデックスがないと何が起きるかというと表ロックが起きる。
どういうことかというと
親表のレコードを更新したり削除する操作を行なう際には子表からも対象レコードを更新、削除する必要が出てくる。
この際、対象レコードを検索するために表ロックがかかる、というわけである。
インデックスが貼られていればインデックススキャンになり子表は行ロックで済むため、インデックスがないことによる表ロックに比べ待ちは起きにくくなる。
インデックスが貼られていない場合
論より証拠ということで、まずはインデックスが貼られていない場合に表ロックが起きることを確認する。
オラクルではテーブルに対して表ロックを許可しないようにすることができる。 コマンドは以下の通り。
alter table testuser.emp disable table lock;
この状態で親表のレコードを削除してみる。
delete from testuser.dept where dept_id=1;
次のコマンドの開始中にエラーが発生しました : 行 49 -
delete from testuser.dept where dept_id=1
エラー・レポート -
ORA-00069: ロックを獲得できません -- EMPに対して表ロックが使用不可になっています
上記の結果の通り、親表のレコードを削除する際に子表empに対して表ロックをかけようとしていることが分かる。
インデックスが貼られている場合
続いてインデックスが貼られている場合の挙動を確認する。
-- インデックス作成のために表ロックを許可する
alter table testuser.emp enable table lock;
-- インデックスの作成
create index testuser.fk_emp on testuser.emp ( dept_id );
-- 表ロックを許可しないようにする
alter table testuser.emp disable table lock;
-- 親表のレコードを削除する
delete from testuser.dept where dept_id=1;
commit;
上記の結果の通り、インデックスが貼られている場合はdeleteでエラーが起きない。 理由は、インデックスが使用されることで表ロックが発生しないからである。
インデックスが不要なケースについて
Expert Oracleによれば、 外部キー制約がある場合でも、以下のケース全部に当てはまる場合はインデックスを貼る必要はないとのこと。
- 親表のレコードの削除をしない
- 親表のユニークキーや主キーの値を更新しない
- 親表から子表への結合をしない
まとめ
無用な待ちを避けるために表ロックを避けるという観点からみて、基本的に外部キー制約に対応するインデックスは貼ったほうが良い。ただでさえ親子関係を明確に規定することになる外部キー制約。無用なロックによる待ちは避けたい。