ヒント句の記述について
前のエントリでこんなことを書きました。
テーブルの結合順序を制御したい！
http://d.hatena.ne.jp/nijimass/20080526/1211806659
LEADINGヒントだと
「ヒント句追加しただけだし、もし間違っててもヒント句なので無視されます！」
とか言うと、実行確認レベルで済む可能性があることです！
# ウソです。コード触ってる以上、ちゃんとテストしましょうね。
ヒント句の特徴として、間違ったヒントを書いてもエラーを返さずに、
無視されて、SQL自体は正常に実行されるというものがあります。
ということで、よく聞く話を。
ヒントの指定で間違いやすいポイントとして、表名の指定があります。
SQL文内で表の別名を使用している場合、
使用している別名でヒントの指定をしないと、
ヒントが有効になりません。
Oracle Database パフォーマンス・チューニング・ガイド 10g リリース2（10.2）
16 オプティマイザ・ヒントの使用方法
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19207-01/hintsref.html#964421
アクセスする表は、文に指定する場合と同じように正確に指定してください。
文が表の別名を使用している場合、表の名前ではなく、表の別名をヒントで使用する必要があります。
スキーマ名が文中にある場合は、ヒント内の表名にそのスキーマ名を入れないでください。
「ヒントがきかないんです！」っていう問い合わせは大体コレか、
そもそも、そういう結合をオプティマイザがしてないのどちらか。
確認してみましょうね。
まず、ベースとなるSQL文です。
SQL> EXPLAIN PLAN FOR
2 SELECT E.ENAME ,E.SAL ,D.DNAME ,S.GRADE
3 FROM EMP E
4 ,DEPT D
5 ,SALGRADE S
6 WHERE E.DEPTNO = D.DEPTNO
7 AND E.SAL > S.LOSAL
8 AND E.SAL <= S.HISAL;
解析されました。
SQL> set linesize 120
SQL> set pagesize 10000
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 721498669
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 94 | 9 (23)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 94 | 9 (23)| 00:00:01 |
| 2 | MERGE JOIN | | 1 | 72 | 8 (25)| 00:00:01 |
| 3 | SORT JOIN | | 5 | 195 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL | SALGRADE | 5 | 195 | 3 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
|* 6 | SORT JOIN | | 12 | 396 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 12 | 396 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 22 | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("E"."SAL"<="S"."HISAL")
6 - access("E"."SAL">"S"."LOSAL")
filter("E"."SAL">"S"."LOSAL")
9 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
- dynamic sampling used for this statement
28行が選択されました。
EMP -> SALGRADE -> DEPT の順で結合してます。
これを EMP -> DEPT -> SALGRADE の順にしたい場合、
このSQLでは、FROM句 で表の別名を指定していますので、
下記のように記述します。
SQL> EXPLAIN PLAN FOR
2 SELECT /*+ LEADING ( E D S ) */
3 E.ENAME ,E.SAL ,D.DNAME ,S.GRADE
4 FROM EMP E
5 ,DEPT D
6 ,SALGRADE S
7 WHERE E.DEPTNO = D.DEPTNO
8 AND E.SAL > S.LOSAL
9 AND E.SAL <= S.HISAL;
解析されました。
SQL> set linesize 120
SQL> set pagesize 10000
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2472009151
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 94 | 12 (25)| 00:00:01 |
| 1 | MERGE JOIN | | 1 | 94 | 12 (25)| 00:00:01 |
| 2 | SORT JOIN | | 12 | 660 | 8 (25)| 00:00:01 |
|* 3 | HASH JOIN | | 12 | 660 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 12 | 396 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
|* 6 | FILTER | | | | | |
|* 7 | SORT JOIN | | 5 | 195 | 4 (25)| 00:00:01 |
| 8 | TABLE ACCESS FULL| SALGRADE | 5 | 195 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."DEPTNO"="D"."DEPTNO")
6 - filter("E"."SAL"<="S"."HISAL")
7 - access(INTERNAL_FUNCTION("E"."SAL")>INTERNAL_FUNCTION("S"."LOSAL")
)
filter(INTERNAL_FUNCTION("E"."SAL")>INTERNAL_FUNCTION("S"."LOSAL")
)
Note
-----
- dynamic sampling used for this statement
29行が選択されました。
はい。意図したとおり、
EMP -> DEPT -> SALGRADE になっていますね。
遅そうですけど。
よく間違う例は、表の実名を書いてしまうパターン。
こんな感じ。
SQL> EXPLAIN PLAN FOR
2 SELECT /*+ LEADING ( EMP DEPT SALGRADE ) */
3 E.ENAME ,E.SAL ,D.DNAME ,S.GRADE
4 FROM EMP E
5 ,DEPT D
6 ,SALGRADE S
7 WHERE E.DEPTNO = D.DEPTNO
8 AND E.SAL > S.LOSAL
9 AND E.SAL <= S.HISAL;
解析されました。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 721498669
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 94 | 9 (23)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 94 | 9 (23)| 00:00:01 |
| 2 | MERGE JOIN | | 1 | 72 | 8 (25)| 00:00:01 |
| 3 | SORT JOIN | | 5 | 195 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL | SALGRADE | 5 | 195 | 3 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
|* 6 | SORT JOIN | | 12 | 396 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 12 | 396 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 22 | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("E"."SAL"<="S"."HISAL")
6 - access("E"."SAL">"S"."LOSAL")
filter("E"."SAL">"S"."LOSAL")
9 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
- dynamic sampling used for this statement
28行が選択されました。
EMP -> SALGRADE -> DEPT で、
一番初めに取った実行計画と同じになってますね。
完璧に無視されてます。
ちなみに、どこまで無視されるかですが、
LEADING に関しては、かっこ内の指定を一つでも間違えたからといって、
LEADINGヒント 全体が無視される訳では無いようです。
たとえば、下記は
DEPT -> SALGRADE -> EMP にしたかったけど、
後ろ二つの指定を間違えた場合。
SQL> EXPLAIN PLAN FOR
2 SELECT /*+ LEADING ( D SALGRADE EMP ) */
3 E.ENAME ,E.SAL ,D.DNAME ,S.GRADE
4 FROM EMP E
5 ,DEPT D
6 ,SALGRADE S
7 WHERE E.DEPTNO = D.DEPTNO
8 AND E.SAL > S.LOSAL
9 AND E.SAL <= S.HISAL;
解析されました。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1826023096
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 94 | 12 (25)| 00:00:01 |
| 1 | MERGE JOIN | | 1 | 94 | 12 (25)| 00:00:01 |
| 2 | SORT JOIN | | 12 | 660 | 8 (25)| 00:00:01 |
|* 3 | HASH JOIN | | 12 | 660 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 12 | 396 | 3 (0)| 00:00:01 |
|* 6 | FILTER | | | | | |
|* 7 | SORT JOIN | | 5 | 195 | 4 (25)| 00:00:01 |
| 8 | TABLE ACCESS FULL| SALGRADE | 5 | 195 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."DEPTNO"="D"."DEPTNO")
6 - filter("E"."SAL"<="S"."HISAL")
7 - access(INTERNAL_FUNCTION("E"."SAL")>INTERNAL_FUNCTION("S"."LOSAL")
)
filter(INTERNAL_FUNCTION("E"."SAL")>INTERNAL_FUNCTION("S"."LOSAL")
)
Note
-----
- dynamic sampling used for this statement
29行が選択されました。
正しく記述できている DEPT だけは最初に読まれていますので、
ヒント自体は有効になっているみたいですね。
ということで、
ヒントが有効になっていないように見える場合はこの辺に
注意してみてください。
