結合の手順の制御

Oracleが提供するヒントが強すぎて、基本的なことを忘れてしまうことがあります。たまにはヒントがないとしたらどんな方法で実行計画を制御しようかを考えてみれば面白いことを見つけるようになるでしょう。

例えば、次のような五つのテーブルがあります。

create table t1(c1, c2)
as 
select
  level, level
from dual
connect by level <= 5000;

create table t2(c1, c2)
as 
select
  level, level
from dual
connect by level <= 4000;

create table t3(c1, c2)
as 
select
  level, level
from dual
connect by level <= 3000;

create table t4(c1, c2)
as 
select
  level, level
from dual
connect by level <= 2000;

create table t5(c1, c2)
as 
select
  level, level
from dual
connect by level <= 1000;

create index t1_n1 on t1(c1);
create index t2_n1 on t2(c1);
create index t3_n1 on t3(c1);
create index t4_n1 on t4(c1);
create index t5_n1 on t5(c1);

exec dbms_stats.gather_table_stats(user, 't1');
exec dbms_stats.gather_table_stats(user, 't2');
exec dbms_stats.gather_table_stats(user, 't3');
exec dbms_stats.gather_table_stats(user, 't4');
exec dbms_stats.gather_table_stats(user, 't5');

テーブルT1, T2, T3, T4, T5を結合するクエリで結合手順はどうなりましょうか。テーブルのデータ分布を見るとT1(c1 between 1 and 10 条件のため）がドライビングテーブルになり、その次はT5(1000件）、T4(2000件）、T3(3000件）、T2(4000件）になるのが分かります。

explain plan for
select 
 *
from
 t1, t2, t3, t5, t4
where
 t1.c1 = t2.c1
 and t1.c1 = t3.c1
 and t1.c1 = t4.c1
 and t1.c1 = t5.c1
 and t1.c2 between 1 and 10
;

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  HASH JOIN            |      |
|*  2 |   HASH JOIN           |      |
|*  3 |    HASH JOIN          |      |
|*  4 |     HASH JOIN         |      |
|*  5 |      TABLE ACCESS FULL| T1   |
|   6 |      TABLE ACCESS FULL| T5   |
|   7 |     TABLE ACCESS FULL | T4   |
|   8 |    TABLE ACCESS FULL  | T3   |
|   9 |   TABLE ACCESS FULL   | T2   |
--------------------------------------

結合の手順をT1 -> T2 -> T3 -> T4 -> T5としたいと言ったらどう？次のようにORDEREDヒントを使えばいいでしょう。またはLEADING(t1 t2 t3 t4 t5)ヒントを使ってもいいです。

explain plan for
select /*+ ordered */
 *
from
 t1, t2, t3, t5, t4
where
 t1.c1 = t2.c1
 and t1.c1 = t3.c1
 and t1.c1 = t4.c1
 and t1.c1 = t5.c1
 and t1.c2 between 1 and 10
;

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  HASH JOIN            |      |
|*  2 |   HASH JOIN           |      |
|*  3 |    HASH JOIN          |      |
|*  4 |     HASH JOIN         |      |
|*  5 |      TABLE ACCESS FULL| T1   |
|   6 |      TABLE ACCESS FULL| T2   |
|   7 |     TABLE ACCESS FULL | T3   |
|   8 |    TABLE ACCESS FULL  | T4   |
|   9 |   TABLE ACCESS FULL   | T5   |
--------------------------------------

仮にヒントを使用せず（そしてRBOではなくてCBOと言えば）、結合の手順が常に上のように出るようにしたいといったらどうすればいいでしょうか。ヒントに慣れてしまえばこのような簡単な質問に答えることが難しくなりかねません。

最も典型的な方法は次のようにt1.c1 = t3.c1の条件をt1.c1 + 0*t2.c1 = t3.c1のような形で使用することです。t3.c1で結合が可能になるためにはt2.c1の値を知らなければならないので、T2 -> T3の手順しか結合方法がありません。同じ原理で条件を付けば全ての結合手順を制御できます。

explain plan for
select 
 *
from
 t1, t2, t3, t4, t5
where
 t1.c1 = t2.c1
 and t1.c1 + 0*t2.c1 = t3.c1  -- t2.c1値なしは t3.c1の結合不可能
 and t1.c1 + 0*t3.c1= t4.c1  -- t3.c1値なしはt4.c1の結合不可能
 and t1.c1 + 0*t4.c1 = t5.c1  -- t4.c1値なしは t5.c1結合不可能
 and t1.c2 between 1 and 10
;

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  HASH JOIN            |      |
|*  2 |   HASH JOIN           |      |
|*  3 |    HASH JOIN          |      |
|*  4 |     HASH JOIN         |      |
|*  5 |      TABLE ACCESS FULL| T1   |
|   6 |      TABLE ACCESS FULL| T2   |
|   7 |     TABLE ACCESS FULL | T3   |
|   8 |    TABLE ACCESS FULL  | T4   |
|   9 |   TABLE ACCESS FULL   | T5   |
--------------------------------------

このような方法を応用すれば結合の手順を自由に制御できます。例えば結合の手順をT1 -> T2 -> T4 -> T3 -> T5（T4がT3より先に結合されるように）とするためにはどうすればいいでしょうか。

explain plan for
select 
 *
from
 t1, t2, t3, t4, t5
where
 t1.c1 = t2.c1
 { ここにどんな条件があれば下のような結合手順が？ }
 and t1.c2 between 1 and 10
;

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  HASH JOIN            |      |
|*  2 |   HASH JOIN           |      |
|*  3 |    HASH JOIN          |      |
|*  4 |     HASH JOIN         |      |
|*  5 |      TABLE ACCESS FULL| T1   |
|   6 |      TABLE ACCESS FULL| T2   |
|   7 |     TABLE ACCESS FULL | T4   |
|   8 |    TABLE ACCESS FULL  | T3   |
|   9 |   TABLE ACCESS FULL   | T5   |
--------------------------------------

解答はもう公開されていたも同然ですからここに書くことはしません。

でもやっぱりヒントが便利ですね。