- 親表を参照する子表が２～３０個ほど存在する。
- すべての外部キー制約にDELETE CASCADE句が指定されている。
- すべての外部キーに適切にインデックスが設定されていている。
- いくつかの子表には数万～数十万のレコードが存在している。
- 統計情報の更新が一度も行われていない。
結局、アナライズを実行することによってパフォーマンスは改善したのですが、外部キー制約でDELETE CASCADE句を指定するときはちょっと気をつけないといけません。
外部キー制約にDELETE CASCADE句を指定したときの実際の動作は、親表のレコードを削除しようとしたときに、まずDELETE CASCADE句付きの外部キー制約を持つ子表のレコードを、削除しようとしている親表のキー値を条件として削除し、その後で親表のレコードを削除するというふうになっています。
例えば、EMP表がDEPT表をDEPTNOを外部キーとして参照しており、この外部キー制約にDELETE CASCADE句が指定されているとして、以下のようにDEPT表からレコードを削除しようとすると、
DELETE DEPT WHERE DNAME = 'SALES';
実際にはまず該当するDEPT表のレコードのDEPTNOを持つEMP表のレコードを削除し、
DELETE EMP WHERE DEPTNO = 30;
続いて、DEPT表のレコードを削除します。
※ちなみにこの動作は子表から全く参照されていない親表のレコードを削除するときでも、同様です。
このとき、子表のレコード数が少なければ問題はないのですが、多い場合は外部キーにインデックスを設定していないと全表走査が発生しパフォーマンスに悪影響を与えてしまうのです。
しかもインデックスを設定していても統計情報を適切に更新していないと結局インデックスが使用されず全表走査が発生してしまうことになります。
（特に統計情報を一度も更新していない状態ではそういう状況に陥りやすいです）
上記の事例では、インデックスは設定されていたものの、統計情報の更新を一度もしていなかったため、各子表のレコードを全表走査が発生し、著しくパフォーマンスが悪かったというわけです。
故に、外部キー制約にDELETE CASCADE句を設定する場合、原則的に外部キーにインデックスを設定すべきです。
また、定期的な統計情報の更新（アナライズ）を徹底すべきです。
特に子表のレコード数が多くなると想定される場合は要注意です。