連結順序とは
複数のテーブルを連結する場合、データベースは一度に2つのデータソースしか連結できないため、オプティマイザーは2つずつの組み合わせで連結順序を指定しなければなりません。また、1つのクエリには複数の連結順序が存在する可能性があります。例えば、select * from t1, t2, t3 where t1.c1 = t2.c1 and t2.c1 = t3.c1; という連結クエリには複数の選択可能な連結順序があり、そのうち2つの例を以下に示します。
Plan 1:
==============================================
|ID|OPERATOR |NAME|EST. ROWS |COST |
----------------------------------------------
|0 |HASH JOIN | |96059601000|22771533844|
|1 | HASH JOIN | |98010000 |23334667 |
|2 | TABLE SCAN|T1 |100000 |42605 |
|3 | TABLE SCAN|T2 |100000 |42605 |
|4 | TABLE SCAN |T3 |100000 |42605 |
==============================================
Plan 2:
==============================================
|ID|OPERATOR |NAME|EST. ROWS |COST |
----------------------------------------------
|0 |HASH JOIN | |96059601000|22771533844|
|1 | HASH JOIN | |98010000 |23334667 |
|2 | TABLE SCAN|T3 |100000 |42605 |
|3 | TABLE SCAN|T2 |100000 |42605 |
|4 | TABLE SCAN |T1 |100000 |42605 |
==============================================
より複雑なクエリでは、例えば select 1 from t1, t2, t3, t4 where t1.c1 = t2.c1 and t1.c1 = t3.c1 and t1.c1 = t4.c1; のように、さらに多くの選択可能な連結順序があり、異なる連結順序によって実行コストに大きな差が生じる場合があります。そのため、オプティマイザーは一定の戦略に基づいて最適な連結順序を選択する必要があります。
オプティマイザーはどのように連結順序を決定するのか
オプティマイザーは、すべてのデータソースが連結されるまで、毎回2つのデータソースを連結し、すべての可能な連結順序を得た後、各連結順序の実行コストを計算し、計算したコストに基づいて最適な連結順序を選択します。
オプティマイザーが連結順序を決定する際に影響を与える要因は非常に多く、例えば：連結するデータ量、テーブルの列アクセス量、データの分布状況、連結アルゴリズム、およびデータテーブルのインデックスなどがあります。その中でも、オプティマイザーが連結順序を選択する際に最も影響を受けやすい要因は連結データ量であり、この要因は統計情報の正確性とオプティマイザーによる行数の推定に依存します。
オプティマイザーに特定の結合順序を使用させるにはどうすればよいですか
オプティマイザーの結合順序の選択を制御するための2種類のヒントがあります。
一つはORDEREDで、オプティマイザーに他の結合順序を考慮せず、クエリで定義された結合順序で結合するよう指示します。例えば、select /*+ORDERED*/ 1 from t1, t2, t3 where t1.c1 = t2.c1 and t1.c1 = t3.c1; の場合、オプティマイザーは t1 join t2 join t3 という結合順序のみを選択して計画を生成します。
==============================================
|ID|OPERATOR |NAME|EST. ROWS |COST |
----------------------------------------------
|0 |HASH JOIN | |96059601000|65343614674|
|1 | HASH JOIN | |98010000 |66674090 |
|2 | TABLE SCAN|T1 |100000 |61860 |
|3 | TABLE SCAN|T2 |100000 |61860 |
|4 | TABLE SCAN |T3 |100000 |61860 |
==============================================
もう一つはLEADINGで、このヒントを使用することで、オプティマイザーに全てまたは一部の結合順序を決定させることができます。例えば、select /*+leading(t1 t3 t4 t2)*/ 1 from t1, t2, t3, t4 where t1.c1 = t2.c1 and t1.c1 = t3.c1 and t1.c1 = t4.c1; のように、完全な結合順序を指定します。
=====================================================
|ID|OPERATOR |NAME|EST. ROWS |COST |
-----------------------------------------------------
|0 |HASH JOIN | |9.414801e+13 |6.412702e+13 |
|1 | HASH JOIN | |96059601000 |65343614674 |
|2 | HASH JOIN | |98010000 |66674090 |
|3 | TABLE SCAN|T1 |100000 |61860 |
|4 | TABLE SCAN|T3 |100000 |61860 |
|5 | TABLE SCAN |T4 |100000 |61860 |
|6 | TABLE SCAN |T2 |100000 |61860 |
=====================================================
同時に一部の結合順序を指定し、残りのテーブルの結合順序はオプティマイザーが決定することもできます。例えば、select /*+leading(t3 t1)*/ 1 from t1, t2, t3, t4 where t1.c1 = t2.c1 and t1.c1 = t3.c1 and t1.c1 = t4.c1; の場合、オプティマイザーは以下のいくつかの結合順序を検討する可能性があります。
Query Plan1:
=====================================================
|ID|OPERATOR |NAME|EST. ROWS |COST |
-----------------------------------------------------
|0 |HASH JOIN | |9.414801e+13 |6.412702e+13 |
|1 | HASH JOIN | |96059601000 |65343614674 |
|2 | HASH JOIN | |98010000 |66674090 |
|3 | TABLE SCAN|T3 |100000 |61860 |
|4 | TABLE SCAN|T1 |100000 |61860 |
|5 | TABLE SCAN |T4 |100000 |61860 |
|6 | TABLE SCAN |T2 |100000 |61860 |
=====================================================
Query Plan2:
=====================================================
|ID|OPERATOR |NAME|EST. ROWS |COST |
-----------------------------------------------------
|0 |HASH JOIN | |9.414801e+13 |6.412702e+13 |
|1 | HASH JOIN | |96059601000 |65343614674 |
|2 | HASH JOIN | |98010000 |66674090 |
|3 | TABLE SCAN|T3 |100000 |61860 |
|4 | TABLE SCAN|T1 |100000 |61860 |
|5 | TABLE SCAN |T2 |100000 |61860 |
|6 | TABLE SCAN |T4 |100000 |61860 |
=====================================================
その後、オプティマイザーはコストを基に最適な結合順序を決定します。
オプティマイザが選択した結合順序が最適でないかどうかを簡単に判断する方法
業務上で遭遇する結合順序の問題は、基本的に統計情報が不正確であるか、行数の推定が不正確であることに起因します。スロークエリの問題が発生した場合、まずベーステーブルの行数の推定が正確かどうかを確認することができます。特定のテーブルの行数の推定に大きな誤差がある場合、オプティマイザが選択した結合順序のコストは基本的に最小ではありません。例えば、select 1 from t1, t2, t3, t4 where t1.c1 = t2.c1 and t1.c1 = t3.c1 and t1.c1 = t4.c1 and t1.c2=2 and t4.c2=1 の実行計画は非常に遅いです。
=========================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
-----------------------------------------
|0 |HASH JOIN | |9686572 |2563762|
|1 | HASH JOIN | |9884 |184590 |
|2 | HASH JOIN | |11 |97710 |
|3 | TABLE SCAN|T1 |10 |48849 |
|4 | TABLE SCAN|T4 |10 |48849 |
|5 | TABLE SCAN |T2 |100000 |42605 |
|6 | TABLE SCAN |T3 |100000 |42605 |
=========================================
SQLを構築してベーステーブルの行数情報を確認します。例えば、T1の行数を確認するには select count(*) from t1 where t1.c2=2; とします。実際の行数が100万行であることがわかった場合、誤った行数の推定に基づいてオプティマイザが選択した結合順序は最適ではありません。そのため、leading hintを使用してオプティマイザが選択する結合順序を制御する必要があります。例えば、leading(t3 (t2 (t4 t1))) とします。