テーブルの結合順序を制御したい！
ということで、今日はヒント句について少し。
皆さんよく使うであろう ORDEREDヒント と ちょっとマイナーな LEADINGヒント についてです。
ORDEREDヒント と LEADINGヒント は「結合順序に関するヒント」です。
つまり、これを使用すれば、
FROM句以下に記述しているテーブルの結合順序を
ユーザ側で制御できるというわけです。
たとえば、下記のようなSQLがあります。
SQL> explain plan for 2 SELECT EMP2.ENAME ,DEPT.DNAME ,EMP2.JOB 3 FROM EMP EMP1 4 ,EMP EMP2 5 ,DEPT 6 WHERE EMP1.MGR = EMP2.EMPNO 7 AND EMP2.DEPTNO = DEPT.DEPTNO 8 AND EMP1.ENAME = 'TURNER'; 解析されました。
すでに解析しちゃいましたが、
「TURNER」さんの上司の名前と組織と役職を検索するSQLです。
# SQL自体の意味はあんまりないので、深くつっこまないでください＞＜
で、実行計画を確認してみます。
SQL> set linesize 120
SQL> set pagesize 10000
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1930541969
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 81 | 5 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 59 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 20 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 39 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 22 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("EMP1"."ENAME"='TURNER')
5 - access("EMP1"."MGR"="EMP2"."EMPNO")
7 - access("EMP2"."DEPTNO"="DEPT"."DEPTNO")
Note
-----
- dynamic sampling used for this statement
25行が選択されました。
EMP1 に対して、EMP2 を結合して、その後 DEPT を結合という流れになっています。
今回の場合、おそらくこの結合順序が最適と思われるので、
別に触らなくてもいいんですけど、
ここで、どうしても DEPT -> EMP2 -> EMP1 の順で結合したい！
となった場合に ORDEREDヒント が登場します。
ORDEREDヒント の使い方
ORDEREDヒント は FROM句 に記述された順序のとおりに、結合を行なうというヒントです。
ですので、今回の要件の場合、
SELECT の 直後に ORDEREDヒント を書き加えて、
FROM句 以下の記述順を変更すればOKです。
じゃあ、解析してみましょう。
SQL> explain plan for
2 SELECT /*+ ORDERED */ EMP2.ENAME ,DEPT.DNAME ,EMP2.JOB
3 FROM DEPT
4 ,EMP EMP2
5 ,EMP EMP1
6 WHERE EMP1.MGR = EMP2.EMPNO
7 AND EMP2.DEPTNO = DEPT.DEPTNO
8 AND EMP1.ENAME = 'TURNER';
解析されました。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 453895177
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 10 (10)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 81 | 10 (10)| 00:00:01 |
|* 2 | HASH JOIN | | 12 | 732 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 12 | 468 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 1 | 20 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP1"."MGR"="EMP2"."EMPNO")
2 - access("EMP2"."DEPTNO"="DEPT"."DEPTNO")
5 - filter("EMP1"."ENAME"='TURNER')
Note
-----
- dynamic sampling used for this statement
23行が選択されました。
めでたく結合順が
DEPT -> EMP2 -> EMP1 になっていますね。
ただ、予想通り、元々の実行計画と比べてコストは2倍になっています。(5 -> 10)
でも、FROM句変更するのってめんどくさいですよね。
変更ミスってこんなことになったりすること多いし。
SQL> explain plan for
2 SELECT EMP2.ENAME ,DEPT.DNAME ,EMP2.JOB
3 FROM ,EMP EMP2
4 ,DEPT
5 EMP EMP1
6 WHERE EMP1.MGR = EMP2.EMPNO
7 AND EMP2.DEPTNO = DEPT.DEPTNO
8 AND EMP1.ENAME = 'TURNER';
FROM ,EMP EMP2
*
行3でエラーが発生しました。:
ORA-00903: 表名が無効です。
カンマuzeeeeeeeeeeeee!!!!!
ということで、そういうときに LEADINGヒント のお目見えです。
LEADINGヒント の使い方
LEADINGヒントは ヒント句内に記述された順序のとおりに、結合を行なうというヒントです。
ですので、今回の要件の場合、SELECT の 直後に LEADINGヒント と
結合順序を書き加えればOKです。FROM句は触る必要がありません。
# ただし、このヒントは9iR2(だったと思う)からの機能です！
SQL書き換えて、解析解析っと。
SQL> explain plan for
2 SELECT /*+ LEADING ( DEPT EMP2 EMP1 ) */ EMP2.ENAME ,DEPT.DNAME ,EMP2.JOB
3 FROM EMP EMP1
4 ,EMP EMP2
5 ,DEPT
6 WHERE EMP1.MGR = EMP2.EMPNO
7 AND EMP2.DEPTNO = DEPT.DEPTNO
8 AND EMP1.ENAME = 'TURNER';
解析されました。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 453895177
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 10 (10)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 81 | 10 (10)| 00:00:01 |
|* 2 | HASH JOIN | | 12 | 732 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 12 | 468 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 1 | 20 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP1"."MGR"="EMP2"."EMPNO")
2 - access("EMP2"."DEPTNO"="DEPT"."DEPTNO")
5 - filter("EMP1"."ENAME"='TURNER')
Note
-----
- dynamic sampling used for this statement
23行が選択されました。
はいできてますね。
LEADINGヒントのいいところは、
ORDEREDヒント使用するために、FROM句を触ることを伝えると、
「コード触ってるわけだし、もう一回ちゃんとテストしてね。」
と確実にいわれてしまい、単体テストをみっちりやり直さなければならなくなるのに対して、
LEADINGヒントだと
「ヒント句追加しただけだし、もし間違っててもヒント句なので無視されます！」
とか言うと、実行確認レベルで済む可能性があることです！
# ウソです。コード触ってる以上、ちゃんとテストしましょうね。
これら２つのヒントの注意点
無理やり結合順序を指定できますので、
「WHERE句で条件の無い表間」での結合を指定できてしまいます。
たとえば、今回のサンプルのSQLですと、
EMP1 と DEPT の間には、直接的な結合条件がありません。
この時、この2表を始めに結合させるような LEADINGヒント を書くと、
どのようなことがおきるでしょうか。解析してみましょう。
SQL> explain plan for
2 SELECT /*+ LEADING ( DEPT EMP1 EMP2 ) */ EMP2.ENAME ,DEPT.DNAME ,EMP2.JOB
3 FROM EMP EMP1
4 ,EMP EMP2
5 ,DEPT
6 WHERE EMP1.MGR = EMP2.EMPNO
7 AND EMP2.DEPTNO = DEPT.DEPTNO
8 AND EMP1.ENAME = 'TURNER';
解析されました。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3257479075
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 13 (8)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 81 | 13 (8)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN| | 4 | 168 | 9 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 4 | 88 | 3 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 1 | 20 | 6 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 1 | 20 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 12 | 468 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP1"."MGR"="EMP2"."EMPNO" AND
"EMP2"."DEPTNO"="DEPT"."DEPTNO")
5 - filter("EMP1"."ENAME"='TURNER')
Note
-----
- dynamic sampling used for this statement
24行が選択されました。
実行計画を見慣れている人ならこれを見ただけで、
「うげええええ！これ組んだやつ死ね！氏ねじゃなくて死ね！」
ってなることでしょう。
ポイントは実行計画の３行目で出てきている「MERGE JOIN CARTESIAN」です。
これは、「直積演算」を行なっていることを示しています。
「直積演算」とは、簡単に言うと、表同士の全組み合わせを生成することです。
仮にここで、DEPTが10行、EMPが3000行あると仮定すると、
直積演算をおこなった段階で、
10 * 3000 = 30000行のレコードが内部的に生成されてしまいます。
このレベルだと、まだなんとか動くかもしれませんが、
通常の企業レベルだと、数千万件クラスのテーブルなんてざらにあると思います。
そんなテーブル同士が直積演算されることを考えると・・・。
ぞっとしますね。メモリがいくらあっても足りません。
ということで、使用する際には、
そのSQLの結合条件に十分注意して、
WHERE句 と整合性が取れるような順序で使用するようにしてください。
とまぁ、ここまで書いておいてなんなんですが、
Oracleも 10.2 11.1 になってきて、
ようやく、オプティマイザがマシになってきました。
基本的には、人間が足りない頭で考えるより、
オプティマイザに考えさせたほうが、優れた実行計画が出てくることがほとんどです。
ですので、チューニングの際は、なんでもかんでもヒント句！ではなく、
まず、オプティマイザを機嫌よく動かすほうに力を注いだほうが良いかもしれません。
ということで、今日はこのへんで。
こういうフリをしたってことは次は統計情報の取得とかを書くべきかな・・・。
では。
