最近携わっているシステムでOracle Databaseを使用しており、参照整合性制約(外部キー制約)の構成列に対してインデックスを作成する・しないの話が出ました。
参照整合性制約の構成列に対しては、インデックスを作成することを基本路線として考えた方がよいと思っていますが、せっかくの機会なので、今回は参照整合性制約の構成列に対してインデックスを作成することによるご利益について調べてみました。
1.参照整合性制約とは
参照整合性制約(外部キー制約)とは、Oracleのマニュアルに記載された説明をベースに書くと、2つのテーブルに1つ以上の共通の列が含まれる場合に、子テーブルで制約が定義される列または列の集合値が、親テーブルで対応する列または列の集合値として存在することをデータベース側で保証する制約です。
ざっくり言うと、親テーブルに存在しないキー値のデータを子テーブルに格納することを許可しない制約、と言ったところです。
参照整合性制約における親テーブル側は必ずインデックスを伴うキー項目になっているため、今回の記事の主役は、子テーブル側の参照整合性制約構成列(上記の図ではemp表のdept_id列)に対する、インデックスの有無となります。
冒頭部分で、インデックスを作成することを基本路線として考えた方がよいと言ったのは、インデックスが構成されないことで、以下の懸念があるためです。
- テーブルロックによる同時実行性低下の懸念
- 非効率なアクセスパスによる処理時間増加の懸念
以降で、それぞれの懸念について説明していきます。
2.テーブルロックによる同時実行性低下の懸念
テーブルデータを更新する際、更新対象テーブルに対してTMエンキューが取得されますが、参照整合性制約が構成されていると、自テーブルだけでなく、参照相手のテーブルのTMエンキューも取得されます。
子テーブルの参照整合性制約構成列に対するインデックスの有無によって、親テーブルの更新時に子テーブル側で取得されるTMエンキューのモードが異なることにより、同時実行性低下の懸念が生じます。
なお、子テーブル側で取得されるTMエンキューのモードは、Oracle Databaseのバージョンにより異なります。今回はOracle19c(19.22.0)の前提で説明いたします。他バージョンの場合など詳細を知りたい方は、My Oracle Supportから下記の情報をご参照ください。
Doc ID 1720454.1: 参照整合性制約とDMLロックについて
2.1 インデックスが構成されていない場合
子テーブルの参照整合性制約構成列にインデックスが構成されていない状態で、親テーブルに対して以下の更新処理を実行した場合、子テーブル側に対する更新処理を阻害する様なモードで、子テーブルに対するTMエンキューが取得されてしまいます。
| 親側の更新処理 | 子側で取得される TMエンキューモード |
|---|---|
| 主キー列のUPDATE | S |
| DELETE(CASCADE無) | S |
| DELETE(CASCADE有) | SSX |
通常のUPDATE/INSERT/DELETEでは、SXモードのTMエンキューを取得する必要があるのですが、上記の親側の更新処理によって子側でTMエンキュー(SまたはSSX)が取得されている間は、子側に対する更新処理に必要なSXモードのTMエンキューが取得できず待機が発生してしまうため、同時実行性が低下します。
※TMエンキューの競合についてはこちらを参照。
※※DELETE(CASCADE有): 親テーブルのレコード削除に合わせて子テーブルのレコードも自動削除
2.2 インデックスが構成されている場合
子テーブルの参照整合性制約構成列にインデックスが構成されている状態で、親テーブルに対して以下の更新処理を実行した場合、子テーブルに対して取得されるTMエンキューのモードが変わります。
| 親側の更新処理 | 子側で取得される TMエンキューモード |
|---|---|
| 主キー列のUPDATE | SX |
| DELETE(CASCADE無) | SX |
| DELETE(CASCADE有) | SX |
上記の親側の更新処理によって子側で取得されるTMエンキューが全てSXモードに変わっています。これは大きな違いで、親側の更新処理によって子側でSXモードのTMエンキューが取得されていても、子側に対する更新処理で必要なSXモードのTMエンキューを取得できるため、同時実行性の低下が発生しません。
補足：TMエンキューのモード間における競合について
TMエンキューのモード間における競合(〇獲得できる/×できない)関係は、以下のようになります。
| 要求するモード | ||||||
|---|---|---|---|---|---|---|
| SS | SX | S | SSX | X | ||
| 既に取得されているモード | SS | ○ | ○ | ○ | ○ | × |
| SX | ○ | ○ | × | × | × | |
| S | ○ | × | ○ | × | × | |
| SSX | ○ | × | × | × | × | |
| X | × | × | × | × | × | |
黄色いセル部分が、上記で述べた親側の更新が先行している状況で、子側の更新処理が発生した場合(インデックス構成無し)に該当する箇所で、水色のセル部分がインデックス構成がある場合に該当する箇所となります。
なお、このTMエンキューの競合関係の詳細については、My Oracle Supportから参照できる、下記の情報が非常に参考になると思います。
Doc ID 1716517.1: TMロック(DMLエンキュー)について
3.非効率なアクセスパスによる処理時間増加の懸念
親テーブル側のDELETE処理(CASCADE有)を実行する際、親テーブルから削除されるレコードのキー値を持ったレコードを、子テーブルからも削除する処理が発生しますが、このとき内部的には以下の様な子テーブルに対するDELETE文が実行されます。
※本記事の「1.参照整合性制約とは」内の図のテーブル構成での例
delete from "HR"."EMP" where "DEPT_ID" = :1
このSQLでレコード削除をするということは、子テーブル側の参照整合性制約構成列にインデックスが存在しないと、子テーブルに対してフルスキャンが発生することになります。子テーブルのレコード数が多い場合、削除処理が長時間化する可能性があります。
また、データメンテナンス等で参照整合性制約を一時的に無効化して有効化する様な運用がある場合、有効化する際に参照整合性制約を阻害するデータが存在しないかチェックするSQLが内部的に実行されるのですが、この処理が子テーブル側の参照整合性制約構成列にインデックスが存在しないと、非効率な実行計画となり長時間化する可能性があります。
私の環境で親テーブル(dept表)を10000件、子テーブル(emp表)を1000000件として、参照整合性制約を有効化したときにSQLトレースを取得して得られた、内部的に実行されたSQLの情報が以下で、インデックスが無い方がアクセスブロックが多く、処理時間も長くなっていることがわかります。
■実行した制約の有効化SQLと内部的に実行されたチェックSQL
実行したSQL ALTER TABLE emp ENABLE CONSTRAINT emp_dept_fk; 内部的に実行されたチェックSQL select /*+ all_rows ordered dynamic_sampling(2) */ A.rowid, :1, :2, :3 from "TEST"."EMP" A , "TEST"."DEPT" B where( "A"."DEPT_ID" is not null) and( "B"."DEPT_ID" (+)= "A"."DEPT_ID") and( "B"."DEPT_ID" is null)
■インデックス無
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 8.28 8.62 10328 906072 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 8.28 8.63 10328 906072 0 0 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 0 0 0 NESTED LOOPS ANTI (cr=906072 pr=10328 pw=0 time=8629556 us starts=1 cost=2843 size=16226 card=427) 1000000 1000000 1000000 TABLE ACCESS FULL EMP (cr=10320 pr=10308 pw=0 time=405497 us starts=1 cost=2842 size=1067450 card=42698) 895747 895747 895747 INDEX UNIQUE SCAN DEPT_ID_PK (cr=895752 pr=20 pw=0 time=1029379 us starts=895747 cost=0 size=105131 card=8087)(object id 24075)
■インデックス有
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 1.70 1.76 2093 2246 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 1.70 1.76 2093 2246 0 0 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 0 0 0 NESTED LOOPS ANTI (cr=2246 pr=2093 pw=0 time=1760195 us starts=1 cost=575 size=16226 card=427) 1000000 1000000 1000000 INDEX FAST FULL SCAN EMP_DEPT_IX (cr=2104 pr=2093 pw=0 time=169215 us starts=1 cost=573 size=1067450 card=42698)(object id 24077) 10000 10000 10000 INDEX UNIQUE SCAN DEPT_ID_PK (cr=142 pr=0 pw=0 time=4854 us starts=10000 cost=0 size=105131 card=8087)(object id 24075)
これは若干おまけ感がありますが、参照整合性制約を構成しているということは、それらのテーブル間で結合する処理も多く存在すると考えられるので、その際に結合列となり得る制約の構成列にインデックスを作成しておいた方が、より効率的なアクセスパスを選択できる可能性が高くなると考えられます。
下記は、子テーブル側にインデックスが存在しないことで、Nested Loop結合の内部表がフルスキャンされるという、一般的には非効率と考えられるアクセスパスが選択されている例です。
SELECT d.dept_name, e.name FROM emp e, dept d WHERE e.dept_id = d.dept_id AND d.dept_id = 30; ■インデックス無 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6 | 138 | 4 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 6 | 138 | 4 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 16 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL | EMP | 6 | 42 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- ■インデックス有 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6 | 138 | 2 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 6 | 138 | 2 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 16 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 6 | 42 | 1 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | EMP_DEPT_IX | 6 | | 0 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------------
4.おわりに
今回の調査結果から、参照整合性制約の構成列に対してインデックスを付与しておくことで、2つの懸念事項を回避できる効果の存在が確認できたと思います。
最後に、この調査をしている最中に思い付いた疑問とそれらに対する私なりの回答を記載しておこうと思います。
参照整合性制約の構成列と完全一致したインデックスじゃないとダメなの？
これはノーです。完全一致している必要はなく、インデックスの構成列が、参照整合性制約の構成列と前方一致していれば大丈夫です。例えば参照整合性制約の構成列が(a, b)としたら、インデックスは(a, b, c)でも大丈夫ということですが、(c, a, b)はダメです。
大丈夫と判断した理由は、完全一致しているインデックスと、前方一致しているインデックスで、親テーブルを更新する際に獲得されるTMエンキューのモードが一致したためです。
参照整合性制約の構成列にインデックスは必須なの？
これは今回の調査結果を否定するかの様な疑問ですが、今回の調査で参照整合性制約の構成列にインデックスを付与することによるご利益の理由がわかったと思います。裏を返せば、そのご利益が得られないシステム(業務)特性であれば、インデックスの付与を必須としなくてもよいのではないかとも思っています。
参照整合性制約観点で言うと、下記全ての操作がないのであれば、インデックスの付与は必須ではないと考えられます。
- 親テーブルの主キー列をUPDATEしない
- 親テーブルのレコードをDELETEしない(CASCADE有/無どちらでも)
- データメンテナンス等で参照整合性制約の無効化/有効化を行わない
「親テーブルと子テーブルの結合がない」を上記観点に入れるか迷いましたが、全くないということは恐らくない気がしていて、だからと言ってインデックス付与を必須と判断してしまうのも少し違う気がしたため、観点からは外しました。
そのため、結合に関しては参照整合性制約の有無に関わらず、テーブル結合におけるアクセス効率に基づいて、インデックス付与を検討すればよいかと思います。
参照整合性制約の構成列に対してインデックスを付与することを基本路線とする考えは変わりませんが、参照整合性制約あるからインデックス付与が絶対！という一方的な考え方はしない様にしたいと思いました。
おまけ：TMエンキュー獲得状況の実機確認方法
親テーブルの更新処理時に子テーブルで獲得される、TMエンキューのモードを実機で確認した方法について、簡単に触れておきたいと思います。
下記のイベントをセットした状態で、親テーブルの更新処理を実行してSQLトレースを取得する方法があるのですが、今回の実機確認では獲得されたエンキュー情報がトレースファイルに出力されなかったため、この方法での取得を断念しました。
ALTER SESSION SET EVENTS '10704 trace name context forever, level 10'; ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
代わりの方法として、下記のように2つのSQL*Plusのセッションを用意し、予め子テーブルに対してLOCK TABLEを実行した状態で、親テーブルの更新処理を実行し、v$lockビューから獲得待機しているTMエンキューのモードを確認する方法を、今回は使用しました。
| Step | セッション1 | セッション2 |
|---|---|---|
| 1 | LOCK TABLE emp IN ROW EXCLUSIVE MODE; | |
| 2 | DELETE FROM dept WHERE dept_id = 30; | |
| 3 | SELECT sid, type, id1, lmode, request, block FROM v$lock WHERE type = 'TM'; | (エンキュー獲得待ち) |
以下はインデックスがない状態で、親テーブルでDELETE(CASCADE有)を実行した際のv$lockの出力例です。
SID TY ID1 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ----------
73 TM 24001 3 0 1
302 TM 24001 0 5 0
302 TM 23996 3 0 2
SID = 73がLOCK TABLE文を実行したセッション1の情報で、ID1 = 24001のテーブル(empテーブル)に対してLMODE = 3(SXモード)を獲得していて、BLOCK = 1であることから、他のセッションを待機させていることがわかります。
SID = 302がDELETE文を実行したセッション2の情報で、LMODE = 0でREQUEST = 5(SSXモード)であることから、SSXモードのTMエンキューを獲得しようとしたが獲得できず待機させられている(= DELETEではSSXモードのTMエンキューを獲得しようとする)ことがわかります。
次の記事：SQLトレースを用いてSQL実行時の処理過程を調べてみた
