「 SQL TuningとHINTの関係 」の第2回目、「 HINTの種類と使い方 」についてお送りしていきます。
「HINTの種類と使い方」については、全6回シリーズで解説していきますが、今回はその1回目をお届けします。

　4.2 HINTの種類と使い方（1/6) 　 – 　結合順序に関するヒント・結合方式に関するヒント　–

SQLの性能問題は、結合順序の異常や間違った結合方法、そして間違ったデータアクセスなど様々な問題が発生します。
このような様々な性能問題を改善するための方法の一つとして使用されるヒントは、オプティマイザに作成者の意図を伝えて効率的な
実行計画を策定できるようにします。
そのため、Oracleは様々な性能問題を改善できる方法でSQLの様々な制御をすることができる多くのヒントを提供しています。
したがって、パフォーマンス問題のタイプに最も適したヒントを適用するためには、どのようなヒントがあるかを知る必要があります。
それぞれのヒントがどのように実行されるかを明確に理解していなければ、適切に使用することができないからです。
この章では、よく使われるヒントとOracle 11gに新しく追加されたヒントについて説明します。
この章に記載されていないヒントについては、Oracle Manualを参照するようにしてください。

Script.各テストで使用するスクリプト

[データを作成する ]データ作成

1.テーブルの説明

* HINT_T1 [加入のお客様］

    cust_no - 顧客番号
    cust_name - 顧客名
    cust_id - 顧客固有の識別子(例えば、住民番号)
    regdate - 登録日

HINT_T2 [加入お客様の住所] * HINT_T2

    addr_no - アドレステーブル識別子
    cust_no - 顧客番号
    addr_div - アドレス区分
    Cust_Addr - 顧客アドレス

HINT_T3 [加入顧客の注文履歴] * HINT_T3

 ord_no - 注文履歴テーブルの識別子
 cust_no - 顧客番号
 goods_no - 商品番号
 orddate - 注文日

* HINT_T4 [商品］

 goods_no - 商品テーブル識別子
 goods_name - 商品名
 regdate - 商品登録日

2.テーブルを作成する

drop table hint_t1 purge ；
drop table hint_t2 purge ；
drop table hint_t3 purge ；
drop table hint_t4 purge ；

create table hint_t1
として
レベルを cust_no として選択します、
       'CUST_'||chr(65+mod(level,26)) as cust_name、
       'ID_'||replace(mod(level,10),0,10) as cust_id、
       to_date('20130101','yyyymmdd')+level-1 as regdate
デュアルから
connect by level <= 100000 ；

create table hint_t2
として
addr_noとしてレベルを選択します、
       replace(mod(level,100000),0,100000) as cust_no、
       mod(level,2) as addr_div、
       decode(replace(mod(level, 10),0,10),1,'Seoul'、
                                          2、「釜山」、
                                          3、「大田」、
                                          4、「仁川」、
                                          5、「済州」、
                                          6、「ソウル」、
                                          7、「釜山」、
                                          8、「ソウル」、
                                          9、「光州」、
                                          10,'cod') as Cust_Addr
デュアルから
connect by level <= 300000 ；

create table hint_t3
として
ord_noとしてレベルを選択します、
       replace(mod(level+100000,50000),0,50000) as cust_no、
       replace(mod(level,1000),0,1000) as goods_no、
       to_date('20130101','yyyymmdd')+level-1 as orddate
デュアルから
connect by level <= 2500000 ；

create table hint_t4
として
level を goods_no として選択します、
       'GOODS_'||chr(65+mod(level,26)) as goods_name、
       to_date('20130101','yyyymmdd')+mod(level,31)-1 as regdate
デュアルから
connect by level <= 100000 ；

3.Indexを作成する

hint_t1 ( cust_no ) に一意のインデックス idx01_hint_t1 を作成します；
hint_t2 ( addr_no ) に一意のインデックス idx01_hint_t2 を作成します；
hint_t2 ( cust_no ) にインデックス idx02_hint_t2 を作成します；
hint_t3 ( ord_no, goods_no, orddate ) に一意のインデックス idx01_hint_t3 を作成します；
hint_t3 ( cust_no ) にインデックス idx02_hint_t3 を作成します；
hint_t3 ( orddate ) にインデックス idx03_hint_t3 を作成します；
hint_t4 ( goods_no ) に一意のインデックス idx01_hint_t4 を作成します；
hint_t4 ( goods_name ) にインデックス idx02_hint_t4 を作成します；
hint_t4 ( regdate ) にインデックス idx03_hint_t4 を作成します；

4.統計情報を生成する

exec dbms_stats.gather_table_stats(ownname=>'system', tabname=>'hint_t1', cascade=>true, estimate_percent=>100) ；
exec dbms_stats.gather_table_stats(ownname=>'system', tabname=>'hint_t2', cascade=>true, estimate_percent=>100) ；
exec dbms_stats.gather_table_stats(ownname=>'system', tabname=>'hint_t3', cascade=>true, estimate_percent=>100) ；
exec dbms_stats.gather_table_stats(ownname=>'system', tabname=>'hint_t4', cascade=>true, estimate_percent=>100) ；

※	場合によっては他のスクリプトによるテストも含まれています。

4.2.1　結合順序に関するヒント

結合順序とは、SQL内にあるテーブルの実行順序を意味します。
このような結合順序は、SQLの性能問題タイプの中で多くの割合を占めるので、SQLにヒントを適用する時、最も重要です。

結合順序を制御するヒントは ORDERED と LEADING ヒントがあります。

ORDEREDはジョイン順序をFROM節にリストされたテーブルの順序で実行したいときに使用し、LEADINGはヒント構文の中にリストされた入力値の順序でジョイン順序を制御したいときに使用します。LEADINGは10g以前は、最初に実行するテーブル一つだけを指定することができましたが、10g以降のバージョンからは複数のテーブルに対して結合順序を制御できるように変更され、より便利な使用が可能になりました。一方、ORDEREDはプログラムのメンテナンス時にFROM節にリストされたテーブルの順序が最初のヒントを適用した時と異なる場合、SQLの性能問題が発生する可能性があるので注意が必要です。 したがって、10g以降のバージョンからは一般的に結合順序を制御するヒントとしてLEADINGヒントを使用するのがより安定した方法です。

・ORDERED

使用バージョン：8.1.0〜。
使用方法: /*+ ORDERED */ /*+ ORDERED
ヒントの意味：FROM節にリストされた順番で結合順序を誘導するヒント。

FROM節にリストされた順序が結合順序になるので、最初のSQLにヒントを適用する時の順序が維持(FROM節のテーブルの順序)され、
SQLが変更された場合、SQLに適用されたヒントが効率的かどうかを必ずチェックしなければなりません。

使用例： 
下記のSQLはT2 -> T3 -> T1 (Table alias)の順にHash Joinを実行することが確認できます。
ORDEREDヒントを使ってジョイン順を変更するようにします。
select t1.*、
       t2.*、
       t3.*。
  from hint_t1 t1、
       hint_t2 t2、
       hint_t3 t3
 where t1.cust_name = :b1
   とt3.orddateは :b2 と :b3 の間です。
   and t1.cust_no = t2.cust_no
   and t2.cust_no = t3.cust_no

-----------------------------------------------------------------------------------
| ID｜操作｜名前｜行｜バイト｜時間｜日付｜時刻
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |       |       |          |
|* 1 | FILTER｜｜｜｜｜｜｜｜｜｜｜フィルター
|* 2 | HASH JOIN | | 4 | 276 | 00:00:33 | 00:00:33
3 |* 3 | HASH JOIN | | 96 | 4320 | 00:00:32 |
| 4 | TABLE ACCESS FULL | HINT_T2 | 300K| 5859K| 00:00:03| 00:00:03
| 5 | TABLE ACCESS BY INDEX ROWID| HINT_T3 | 32 | 800 | 00:00:01 | HINT_T3
|* 6 | INDEX RANGE SCAN | IDX03_HINT_T3 | 32 | | 00:00:01 |
|* 7 | TABLE ACCESS FULL | HINT_T1 | 3846 | 92304 | 00:00:02 | 00:00:02
-----------------------------------------------------------------------------------

例[1].HINT_T1 -> HINT_T2 -> HINT_T3 結合順序の変更
select /*+ ordered */
       t1.*、
       t2.*、
       t3.*。
  from hint_t1 t1, --- ORDERED ヒントとFROM節のテーブル順序が一致する
       hint_t2 t2、
       hint_t3 t3
 where t1.cust_name = :b1
とt3.orddateは :b2 と :b3 の間です。
   and t1.cust_no = t2.cust_no
   and t2.cust_no = t3.cust_no；

---------------------------------------------------------------------------------
| ID｜操作｜名前｜行｜バイト｜時間｜日付｜時刻
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |       |       |          |
|* 1 | FILTER｜｜｜｜｜｜｜｜｜｜フィルター
|* 2 | TABLE ACCESS BY INDEX ROWID | HINT_T3 | 1 | 25 | 00:00:01 | 00:00:01
| 3 | NESTED LOOPS | | 11538 | 777K| 03:11:20 | NESTED LOOPS
| 4 | NESTED LOOPS | | 11538 | 495K| 03:04:24 | NESTED LOOPS
|* 5 | TABLE ACCESS FULL | HINT_T1 | 3846 | 92304 | 00:00:02 | 00:00:02
|* 6 | TABLE ACCESS FULL | HINT_T2 | 3 | 60 | 00:00:03 | 00:00:03
|* 7 | INDEX RANGE SCAN | IDX03_HINT_T3 | 32 | | 00:00:01 |
---------------------------------------------------------------------------------

例[2].HINT_T3 -> HINT_T1 -> HINT_T2 結合順序の変更
ORDEREDヒントを付与して、FROM節にリストされた順に(HINT_3, HINT_T1, HINT_T2) ジョイン順を変更しました。
select /*+ ordered */
       t1.*、
       t2.*、
       t3.*。
  from hint_t3 t3, --- ORDERED ヒントとFROM節のテーブル順序が一致する
       hint_t1 t1、
       hint_t2 t2
 where t1.cust_name = :b1
 とt3.orddateは :b2 と :b3 の間です。
   and t1.cust_no = t2.cust_no
   and t2.cust_no = t3.cust_no；
 
------------------------------------------------------------------------------------
| ID｜操作｜名前｜行｜バイト｜時間｜時間｜日付｜時間｜時刻
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |       |       |          |
|* 1 | FILTER｜｜｜｜｜｜｜｜｜｜フィルター
| 2 | MERGE JOIN | | 11538 | 777K| 00:02:09 | MERGE JOIN
| 3 | SORT JOIN | | 123K| 5889K| 00:01:14 | SORT JOIN
| 4 | MERGE JOIN CARTESIAN | | 123K| 5889K| 00:00:37 | 00:00:37
| 5 | TABLE ACCESS BY INDEX ROWID| HINT_T3 | 32 | 800 | 00:00:01 | 00:00:00
|* 6 | INDEX RANGE SCAN | IDX03_HINT_T3 | 32 | | 00:00:01 |
| 7 | BUFFER SORT | | 3846 | 92304 | 00:00:37 | 00:00:37
|* 8 | TABLE ACCESS FULL | HINT_T1 | 3846 | 92304 | 00:00:02 | 00:00:02
|* 9 | SORT JOIN | | 300K| 5859K| 00:00:55 | SORT JOIN
| 10 | TABLE ACCESS FULL | HINT_T2 | 300K| 5859K| 00:00:03 | 00:00:03
------------------------------------------------------------------------------------

・リーディング

使用バージョン：8.1.6〜。
使用方法: /*+ LEADING(T1) */ または /*+ LEADING(T1 T2 T3) */ 使用方法
ヒント意味：Driving Tableを指定するヒント

10gから /*+ LEADING(T1 T2 T3) */ のように複数のテーブルの順序を制御できるようになり、OREDREDヒントの代わりにLEADING
ヒントで結合順序をより簡単に調整することができます。
そのため、10g以降のバージョンでは、結合順序を制御するヒントとしてほとんどLEADINGを使います。

使用例：
select /*+ leading(t1 t3 t2) */ を選択します。
       t1.*、
       t2.*、
       t3.*。
  from hint_t1 t1, ---> LEADING ヒントとFROM節のテーブルの順番が違う。
       hint_t2 t2、
       hint_t3 t3
 where t1.cust_name = :b1
とt3.orddateは :b2 と :b3 の間です。
   and t1.cust_no = t2.cust_no
   and t2.cust_no = t3.cust_no；

------------------------------------------------------------------------------------
| ID｜操作｜名前｜行｜バイト｜時間｜時間｜日付｜時間｜時刻
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |       |       |          |
|* 1 | FILTER｜｜｜｜｜｜｜｜｜｜｜フィルター
| 2 | TABLE ACCESS BY INDEX ROWID | HINT_T2 | 1 | 20 | 00:00:01 | HINT_T2
| 3 | NESTED LOOPS | | 11538 | 777K| 01:16:16 | NESTED LOOPS
| 4 | NESTED LOOPS | | 123K| 5889K| 00:02:20 | NESTED LOOPS
|* 5 | TABLE ACCESS FULL | HINT_T1 | 3846 | 92304 | 00:00:02 | 00:00:02
| 6 | TABLE ACCESS BY INDEX ROWID| HINT_T3 | 32 | 800 | 00:00:01 | HINT_T3
|* 7 | INDEX RANGE SCAN | IDX03_HINT_T3 | 32 | | 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX02_HINT_T2 | 1 | | 00:00:00:01
------------------------------------------------------------------------------------

4.2.2　結合方式に関するヒント

テーブル間のジョインをどのような方法で行うかをヒントで決めることができます。
ジョイン方式は、代表的にNested Loops Join、Hash Join、Merge Joinがあり、これを制御するヒントとしてそれぞれUSE_NL、USE_HASH、USE_MERGEがあります。
SQLチューニング時、ジョイン方式を制御するヒントだけを適用する場合、チューニングした時点のジョイン順序が変更される危険性が
あるので、ジョイン順序を決定するヒント(ORDERED, LEADING)と一緒に適用できるようにしなければなりません。
各ジョイン方法の詳細については、すでに複数のコンテンツで説明されているので、本章では説明を省略します。

・USE_NL

使用バージョン：8.1.0〜。 
使用方法: /*+ USE_NL(T2) */ または /*+ USE_NL(T1 T2) */ を使用します。
ヒントの意味：テーブル間ジョイン方式をNested Loops Joinに誘導するヒント

USE_NLヒントを使用すると、二つのテーブル間の結合方式は、先行テーブルから抽出した件数だけ後行テーブルが繰り返し実行するNested Loops Join方式でSQLが実行されます。Nested Loops Joinは後行テーブルの実行回数とインデックスの効率性によってSQLの性能が左右されるので、下記の二つの場合に使用することが効率的なSQLの性能を誘導することができます。

・　後行テーブルのジョイン接続カラムにインデックスが存在しなければなりません。インデックスが存在しない場合、後行テーブルを
先行テーブルから抽出した件数（結合件数）だけTable Full Scanを実行する非効率が発生します。
・　Nested Loops Joinは後続テーブルのインデックスから抽出したデータでテーブルアクセス時に発生するRandom I/O量が多い場合、性能が急激に落ちるという欠点があります。従って、後行テーブルが持つ定数条件で実行する方が有利な場合は、USE_HASHヒントを使う方が有利です。
また、ジョインされるデータ件数(先行テーブルから抽出したデータ)が少ない場合に有利です。

　下記の使用例の内容は、DBMS_XPLAN.DISPLAY_CURSORの内容の一部データです。

使用例：
select /*+ leading(t2 t3) use_nl(t3) */ --- 結合順序決定のヒントのように使う
t2.*、
       t3.*。
from hint_t2 t2、
       hint_t3 t3
where t2.cust_addr=:b1
and t3.cust_no = t2.cust_no
と t3.orddate を :b2 と :b3 の間に配置します；

---------------------------------------------------------------------------------
| ID｜操作｜名前｜行｜バイト｜時間｜時間｜日付｜時間｜時刻
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | | | | | | 0
|* 1 | FILTER｜｜｜｜｜｜｜｜｜｜｜フィルター
|* 2 | TABLE ACCESS BY INDEX ROWID| HINT_T3 | 1 | 25 | 00:00:01 | HINT_T3
| 3 | NESTED LOOPS | | 37 | 1665 | 00:25:49 | NESTED LOOPS
|* 4 | TABLE ACCESS FULL | HINT_T2 | 42857 | 837K| 00:00:03 | 00:00:03
|* 5 | INDEX RANGE SCAN | IDX03_HINT_T3 | 32 | | 00:00:01 |
---------------------------------------------------------------------------------

・　USE_HASH

使用バージョン：8.1.0〜。
使用方法: /*+ USE_HASH(T2) */ または /*+ USE_HASH(T1 T2) */ を使用します。
ヒントの意味テーブル間のジョイン方式をHash Joinに誘導するヒント

T2テーブルから抽出されたデータでHash Tableを作成し、T3テーブルのデータでProbeしてデータを抽出します。
後行テーブルがNested Loops Joinで実行されるよりHash Joinが効率的な場合は、通常、先行テーブルからデータが多く抽出され、
結合される後行テーブルにアクセス量が多い場合と、後行テーブルの結合カラムに効率的なインデックスが生成されていない場合が
代表的な例です。

使用例：
select /*+ leading(t2 t3) use_hash(t3) */ /*+ leading(t2 t3)
t2.*、
       t3.*。
from hint_t2 t2、
       hint_t3 t3
where t2.cust_addr=:b1
and t3.cust_no = t2.cust_no
と t3.orddate を :b2 と :b3 の間に配置します；

----------------------------------------------------------------------------------
| ID｜操作｜名前｜行｜バイト｜時間｜時間｜日付｜時間｜時刻
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | | | | | | 0
|* 1 | FILTER｜｜｜｜｜｜｜｜｜｜｜フィルター
|* 2 | HASH JOIN | | 37 | 1665 | 00:00:08 | 00:00:08
|* 3 | TABLE ACCESS FULL | HINT_T2 | 42857 | 837K| 00:00:03 | 00:00:03
| 4 | TABLE ACCESS BY INDEX ROWID| HINT_T3 | 32 | 800 | 00:00:01 | 00:00:00
|* 5 | INDEX RANGE SCAN | IDX03_HINT_T3 | 32 | | 00:00:01 |
----------------------------------------------------------------------------------

・　USE_MERGE

使用バージョン：8.1.0〜。
使用方法: /*+ USE_MERGE(T2) */ または /*+ USE_MERGE(T1 T2) */ を使用します。
ヒントの意味：テーブル間の結合方式をMerge Joinに誘導するヒント

テーブル間または結合結果セットとテーブルの結合方式をMerge Joinに誘導するヒントです。
一般的にMerge JoinよりHash Joinが性能上有利なので、あまり使われません。

使用例：
select /*+ leading(t2 t3) use_merge(t3) */...
       t2.*、
       t3.*。
from hint_t2 t2、
       hint_t3 t3
where t2.cust_addr=:b1
and t3.cust_no = t2.cust_no
と t3.orddate を :b2 と :b3 の間に配置します；

-----------------------------------------------------------------------------------
| ID｜操作｜名前｜行｜バイト｜時間｜時間｜日付｜時間｜時刻
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |       |       |          |
|* 1 | FILTER｜｜｜｜｜｜｜｜｜｜｜フィルター
| 2 | MERGE JOIN | | 37 | 1665 | 00:00:09 | 00:00:09
| 3 | SORT JOIN | | 42857 | 837K| 00:00:09 | SORT JOIN
|* 4 | TABLE ACCESS FULL | HINT_T2 | 42857 | 837K| 00:00:03 | 00:00:03
|* 5 | SORT JOIN | | 32 | 800 | 00:00:01 | 00:00:01
| 6 | TABLE ACCESS BY INDEX ROWID | HINT_T3 | 32 | 800 | 00:00:01 | 00:00:01
|* 7 | INDEX RANGE SCAN | IDX03_HINT_T3 | 32 | | 00:00:01 |
-----------------------------------------------------------------------------------

・　SWAP_JOIN_INPUTS

使用バージョン：8.1.0〜。
使用方法: /*+ SWAP_JOIN_INPUTS(T1) */ (Inverse: NO_SWAP_JOIN_INPUTS)
ヒント意味: Hash Joinで実行される Outer Join Tableを先に実行する場合に使用するヒント。

Outer Joinを実行するように作成されたSQLの結合順序はすでに決定されています。
もし、既に決められた結合順序を変更したい場合は、SWAP_JOIN_INPUTSヒントを使うことを検討します。

下記のSQLはT1テーブルが先行テーブルになり、T1テーブルとT2テーブルはHash Joinで実行されるSQLです。

使用例：
SELECT T1.* 、
       T2.*。
FROM HINT_T1 T1 、
       HINT_T2 T2
WHERE T1.CUST_NO = T2.CUST_NO( + )；

---------------------------------------------------------------------------------------- 
| ID｜操作｜名前｜E-Rows｜A-Rows｜A-Time｜バッファ｜読み取り｜読み取り｜読み出し 
---------------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 232K|00:00:05.15 | 9342 | 1386 | 
|* 1 | HASH JOIN OUTER | | 300K| 232K| 00:00:05.15 | 9342 | 1386 | 
| 2 | TABLE ACCESS FULL | HINT_T1 | 100K| 100K|00:00:00:00.26 | 419 | 417 | HINT_T1 
| 3 | TABLE ACCESS FULL | HINT_T2 | 300K| 300K| 00:00:00:00.87 | 8923 | 969 | 
----------------------------------------------------------------------------------------

しかし、SWAP_JOIN_INPUTSヒントを適用すると、Outer Joinであっても下記のようにジョイン順序を変更することができます。
ただし、Hash Join以外の結合方法は、SWAP_JOIN_INPUTSヒントを適用しても結合順序の変更ができません。

SELECT /*+ SWAP_JOIN_INPUTS(T2) */ /*+ SWAP_JOIN_INPUTS(T2)
T1.* 、
       T2.*。
FROM HINT_T1 T1 、
       HINT_T2 T2
WHERE T1.CUST_NO = T2.CUST_NO( + )；

-------------------------------------------------------------------------------------------
| ID｜操作｜名前｜E-Rows｜A-Rows｜A-Time｜バッファ｜読み取り｜読み取り｜読み出し
-------------------------------------------------------------------------------------------
0 | SELECT STATEMENT | | 232K|00:00:04.98 | 10666 | 1384 | 10666 | 0 | SELECT STATEMENT
|* 1 | HASH JOIN RIGHT OUTER | | 300K| 232K|00:00:04.98 | 10666 | 1384 |
2 | TABLE ACCESS FULL | HINT_T2 | 300K| 300K|00:00:00:00.76 | 971 | 968 | 971 | 968 | HINT_T2
| 3 | TABLE ACCESS FULL | HINT_T1 | 100K| 100K|00:00:00:00.34 | 9695 | 416|
-------------------------------------------------------------------------------------------

Hash JoinもNested Loops Joinのようにデータ件数が少ないセットが先行テーブルとして指定することが有利です。
したがって、Outer Joinを実行しなければならない二つのセット間のデータ数を比較して、Probeテーブルを先行テーブルに
変更することが性能上で有利かどうかを判断した上でヒントを適用すればよいのです。

SQLチューニングブログ 2nd Season（第19回）　終

次回のSQLチューニングブログは

SQLチューニングブログ 2nd Season （第4章）

「SQL Tuning と HINTの関係」（3/8） HINTの種類と使い方（第2回）

日本エクセムのデータベースソリューション

最新情報は公式SNSでも配信中

私たちはITインフラにおける

プロジェクト運営 ～ システム運用 の安定化と効率化を推進します。

SQLチューニングブログについてのお問い合わせは

日本エクセム株式会社
営業推進部

✉ sales@ex-em.co.jp