接続順序とは
複数テーブルの結合では、データベースが一度に結合できるデータソースは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; のように、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)))。