結合順序とは
複数テーブルの結合では、データベースは一度に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種類あります。
1つ目はORDEREDで、オプティマイザーに他の結合順序を検討せず、クエリで定義された結合順序で結合するよう指示します。例えば select /*+ORDERED*/ 1 from t1, t2, t3 where t1.c1 = t2.c1 and t1.c1 = t3.c1; の場合、オプティマイザーは t1 join t2 join t3 という1つの結合順序のみを選択して実行計画を生成します。
==============================================
|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 |
==============================================
もう1つは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; のように、LEADINGで完全な結合順序を指定します。
=====================================================
|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ヒントを使用してオプティマイザーが選択する結合順序を制御する必要があります。例えば leading(t3 (t2 (t4 t1))) などです。