Oracleのヒント句を使いこなす
ヒント句は、Oracleのオプティマイザが作成する実行計画を制御するヒントです。
ヒント句を使用することで、結合順、結合方法をオプティマイザに任せず指定することができます。
ヒント句は通常のコメントの構文で書きます。
以下のようにコメント（/* /）の開始（/）の直後に(+)をつけるのがポイントです。
/*+ ヒント句 */
具体的には、以下のようにSELECTの後にヒント句を記載します。
例ではINDEXヒントを指定しており、指定したテーブルに対してインデックススキャンを実行します。
select /*+ INDEX(employees emp_department_ix)*/ * from ～
複数のヒントを使用する場合は、以下のようにヒント（例ではLEADINGとUSE_NL）を並べて指定します。
select /*+ LEADING (e d) USE_NL(d) */ * from ～
また、ヒント句でのテーブル名は実名でなく別名（エイリアス）を指定します。
ヒントが使用されない例）
select /*+ FULL(DEPARTMENTS) */ * from hr.DEPARTMENTS d where d.manager_id = 200;
ヒントが使用される例）
select /*+ FULL(d) */ * from hr.DEPARTMENTS d where d.manager_id = 200;
別名を使用していない場合は、以下のようにテーブル名をそのまま指定できます。
select /*+ FULL(DEPARTMENTS) */ * from hr.DEPARTMENTS where manager_id = 200;
ヒント句はいつ使う
通常はOracleのオプティマイザが統計情報を参照して最適な実行計画を選択してくれています。ただし、オプティマイザ統計が古くて実態とかけ離れていたり、複雑なSQLの場合（7つ以上の表を結合するなど）は最適な実行計画とならないことがあります。
そのようなときに実行計画に対するヒントを使用します。
このように便利なヒント句ですが、実際のデータが変わることにより、使用したヒントで作成された実行計画が最適ではなくなることがあります。ヒント句がなければオプティマイザが最適な実行計画を選択するのですが、ヒント句があるために最適な実行計画が選ばれなくなってしまいます。
実行計画の確認
ヒント句がオプティマイザに使用されて実行計画に反映されていることを確認するために、実行計画を確認します。
実行計画を確認する方法はいくつもありますが、ここではSQLPlusで接続し、DBMS_XPLANを使用します。
SQLPlusでDBへ接続し、以下のようにコマンドを実行して確認します。
set serveroutput off
set linesize 1000
set pagesize 0
※実行計画を表示するSQLを実行する。
select /*+ FULL(d) */ * from hr.DEPARTMENTS d where d.manager_id = 200;
SQL実行後、DBMS_XPLAN.DISPLAY_CURSORを使用して直前に実行したSQLの実行計画を表示します。
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALL LAST'));
※一部省略。formatは色々とオプションがあります。最低限で良ければBASICを指定します。
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 21 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
1 - SEL$1 / D@SEL$1
- FULL(d)
ヒント句を扱うには実行計画が読める必要がありますが、実行計画の読み方はここでは扱いません（参考になるサイトがたくさんあるので）
ヒント句の例
ヒント句はたくさんありますが、実際によく使うヒントはLEADING, USE_NL, INDEX, FULL, USE_HASHぐらいで、覚えるのはそう大変ではありません。
ヒント句の一覧は以下を参照してください。
- SQL言語リファレンス(19c) ヒントのリスト(アルファベット順)
- SQL言語リファレンス(12.2) ヒントのリスト(アルファベット順)
- Oracle SQL の Hint（ヒント）句まとめ（一覧）
以降によく使用する5つのヒントについて説明していきます。
(1) LEADINGヒント
LEADINGヒントは、表を結合する順序を指定できます。
大抵はUSE_NL/USE_HASHヒントと組み合わせて利用します。
- 構文： LEADING([表名] [表名] ・・・)
- 例： /*+ LEADING(a b c)
(2) USE_NLヒント
USE_NLヒントは、指定された各表をネステッドループ結合(nested loop join)で結合します。
- 構文： USE_NL([内部表1] [内部表2] ・・・)
- 例： /*+ LEADING(A B C) USE_NL(B C) */
USE_NL(外部表 内部表)と指定している例もありますが、それは間違いで、LEADINGヒントと一緒に使用します。
以下は実行例です。
※最適化としては意味がありませんが実行計画の行数が少なくなるようにFULLヒントを使用しています。
select /*+ FULL(b) FULL(a) LEADING(a b) USE_NL(b) */ *
from departments a,
employees b
where a.manager_id = b.manager_id;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 20 (100)| |
| 1 | NESTED LOOPS | | 65 | 5850 | 20 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPARTMENTS | 11 | 231 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMPLOYEES | 6 | 414 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
(3) USE_HASHヒント
USE_HASHヒントは、指定された各表をハッシュ結合を使用して結合します。
通常LEADINGヒントと一緒に使用します。
- 構文： USE_HASH([内部表1] [内部表2] ・・・)
- 例： /*+ LEADING(A B C) USE_HASH(B C) */
以下は実行例です。
select /*+ FULL(b) FULL(a) USE_HASH(b) */ *
from departments a,
employees b
where a.manager_id = b.manager_id;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | HASH JOIN | | 65 | 5850 | 6 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPARTMENTS | 11 | 231 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
3つの表でハッシュ結合した場合の例です。
LEADINGヒントでa,b,cと指定しているので、最初にaとbで結合し、その後にその結果とcを結合してます。
select /*+ FULL(c) FULL(b) FULL(a) LEADING(a b c) USE_HASH(b c) */ *
from departments a,
employees b,
locations c
where a.manager_id = b.manager_id
and a.location_id = c.location_id;
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
|* 1 | HASH JOIN | | 65 | 9035 | 9 (0)| 00:00:01 |
|* 2 | HASH JOIN | | 65 | 5850 | 6 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPARTMENTS | 11 | 231 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | LOCATIONS | 23 | 1127 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
また、ハッシュ表を指定する場合は、SWAP_JOIN_INPUTSヒントを使用します。
SWAP_JOIN_INPUTS(表別名)で、指定した表がハッシュ表として利用されるようになります。
(4) FULLヒント
FULLヒントは、指定した表に対してフルスキャンを実行します。
- 構文： FULL([表名])
- 例： /*+ FULL(A) */
以下はFULLヒントを使用した実行例です。
select /*+ FULL(b) */ *
from
employees b
where b.last_name = 'King';
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 69 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
(5) INDEXヒント
FULLヒントは、指定した表に対してインデックススキャンを実行します。
- 構文： INDEX([表名] [インデックス名])
- 例： /*+ INDEX(A IDX) */
※表の別名AのIDXインデックスを使用する場合
以下はINDEXヒントを使用した実行例です。
select /*+ INDEX(b EMP_NAME_IX) */ *
from
employees b
where b.last_name = 'King';
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 69 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_NAME_IX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
最後にQB_NAMEヒント
よく使用する5つのヒントの説明は以上ですが、他にQB_NAMEヒントも使うことがあるので説明します。
QB_NAMEヒントは問い合わせブロックに対して、名前をつけるためのヒントです。
通常、問い合わせブロックはオプティマイザによりSEL$1などの名前が自動でつけられます。QB_NAMEヒントではこの名前を自由に指定することができます。
QB_NAMEヒントはそれ自体では実行計画を制御するわけではありませんが、名前を付けることで実行計画が読みやすくなります。また、ヒントをつけるときに問い合わせブロック名を指定する際に使いやすくなるため、使う人もいるかなと思います。
- 構文： QB_NAME([問い合わせブロック名])
- 例： /*+ QB_NAME(TQB) */
select /*+ QB_NAME(TEST_QB) */ * from hr.DEPARTMENTS where manager_id = 200;
他のヒントのつけ方
これまで説明してきたヒントのつけ方が通常のヒント句ですが、他にも問合せブロック付きヒント、グローバル・ヒントといったヒントのつけ方があります。
問合せブロック付きヒント
問合せブロック付きヒントは、ヒントをつける問合せブロックを指定する場合に使用します。問合せブロック付きヒントを指定することで、任意の問合せブロックにヒントをつけることができます。
次のSQLと実行計画について、問合せブロックを使用してみます。
set serveroutput off
set linesize 1000
set pagesize 0
select em.last_name,
de.department_name
from departments de,
employees em
where de.manager_id = em.manager_id
and de.location_id in (select lo.location_id
from locations lo,
countries co
where lo.country_id = co.country_id
and co.country_name = 'United States of America');
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALL LAST'));
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | VIEW | VM_NWVW_2 | 5 | 155 | 5 (20)| 00:00:01 |
| 2 | HASH UNIQUE | | 5 | 315 | 5 (20)| 00:00:01 |
| 3 | NESTED LOOPS | | 5 | 315 | 4 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 6 | 315 | 4 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 39 | 3 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 2 | 36 | 2 (0)| 00:00:01 |
|* 7 | INDEX FULL SCAN | COUNTRY_C_ID_PK | 1 | 12 | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| LOCATIONS | 2 | 12 | 1 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | LOC_COUNTRY_IX | 2 | | 0 (0)| |
|* 10 | TABLE ACCESS BY INDEX ROWID BATCHED | DEPARTMENTS | 1 | 21 | 1 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 4 | | 0 (0)| |
|* 12 | INDEX RANGE SCAN | EMP_MANAGER_IX | 6 | | 0 (0)| |
| 13 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 6 | 144 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$B186933D / VM_NWVW_2@SEL$2F1334C4
2 - SEL$B186933D
7 - SEL$B186933D / CO@SEL$2
8 - SEL$B186933D / LO@SEL$2
9 - SEL$B186933D / LO@SEL$2
10 - SEL$B186933D / DE@SEL$1
11 - SEL$B186933D / DE@SEL$1
12 - SEL$B186933D / EM@SEL$1
13 - SEL$B186933D / EM@SEL$1
上の副問合せ中のcountryテーブルは以下のようにインデックスレンジスキャン（INDEX RANGE SCAN）が選択されています。
これをフルスキャンとなるように問合せブロック付きヒントをつけてみます。
|* 9 | INDEX RANGE SCAN | LOC_COUNTRY_IX | 2 | | 0 (0)| |
実行計画中の[Query Block Name]が問合せブロック名になります。
該当の問合せは以下の部分になります。
右側が問合せブロックの別名で、@以降が問合せブロック名になります。
9 - SEL$B186933D / LO@SEL$2
先ほど確認した、問い合わせブロック名（@SEL$2）を指定したヒント句は以下のようになります。
locationテーブルが「TABLE ACCESS FULL」となっていることが分かります。
select /*+ FULL(@SEL$2 lo) */ em.last_name,
de.department_name
from departments de,
employees em
where de.manager_id = em.manager_id
and de.location_id in (select lo.location_id
from locations lo,
countries co
where lo.country_id = co.country_id
and co.country_name = 'United States of America');
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALL LAST HINT_REPORT'));
※一部省略
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | VIEW | VM_NWVW_2 | 5 | 155 | 6 (17)| 00:00:01 |
| 2 | HASH UNIQUE | | 5 | 315 | 6 (17)| 00:00:01 |
| 3 | NESTED LOOPS | | 5 | 315 | 5 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 6 | 315 | 5 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 39 | 4 (0)| 00:00:01 |
| 6 | NESTED LOOPS SEMI | | 2 | 36 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | LOCATIONS | 23 | 138 | 3 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | COUNTRY_C_ID_PK | 1 | 12 | 0 (0)| |
|* 9 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENTS | 1 | 21 | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 4 | | 0 (0)| |
|* 11 | INDEX RANGE SCAN | EMP_MANAGER_IX | 6 | | 0 (0)| |
| 12 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 6 | 144 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
7 - SEL$B186933D / LO@SEL$2
- FULL(@SEL$2 lo)
グローバル・ヒント
ビューにヒントをつけるのは、Oracle公式ドキュメントに以下の記載があるように推奨されていません。
あるコンテキストで定義されたビューが、別のコンテキストで使用される可能性があるため、ビュー内、またはビュー(副問合せ)に対するヒントはお薦めできません。
19.3 ビューでのヒントの使用方法
https://docs.oracle.com/cd/E16338_01/server.112/b56312/hintsref.htm
ビューに対してはここで紹介するグローバル・ヒントを使用します。
グローバル・ヒントは、ヒントの表指定を以下の形式で行います。
- '<ビュー名>.<表名>'
そのため、ビューにヒントを指定する場合は、主問合せから最も簡単に指定することが可能です（インライン・ビューでも使用できます）。
以下はビュー'v1'内でインデックスを使用するために、INDEXヒントをグローバル・ヒントで指定している例です。
CREATE OR REPLACE VIEW v AS
SELECT e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
FROM employees e1, ( SELECT * FROM employees e3) e2, job_history j
WHERE e1.employee_id = e2.manager_id
AND e1.employee_id = j.employee_id
AND e1.hire_date = j.start_date
AND e1.salary = ( SELECT max(e2.salary) FROM employees e2
WHERE e2.department_id = e1.department_id )
GROUP BY e1.first_name, e1.last_name, j.job_id
ORDER BY total_sal;
select /*+ INDEX(v.e2.e3 emp_job_ix) */ * from v;
グローバル・ヒントを使用することで、ビューを変更せずにヒントをつけることができました。
問合せブロック付きヒントを使用して、以下のように指定しても同じ結果となります。
select /*+ INDEX(@SEL$2 e2.e3 emp_job_ix) */ * from v;
select /*+ INDEX(@SEL$3 e3 emp_job_ix) */ * from v;
ヒントレポート（HINT REPORT）
ヒントはコメントであるため記述に誤りがあってもエラーにはなりません。そのため、逆にヒントが正しく書けて適用されているかどうかも確認するのが難しいです。
Oracle19cはヒントレポートを出力できるようになり、ヒントが実行計画で使用されているかどうか、エラーならその理由を確認することができます。
ヒントレポートは実行計画にて出力することができます。
set serveroutput off
select /*+ LEADING (e d) USE_NL(d) */
d.*
, e.FIRST_NAME
, e.LAST_NAME
, e.EMAIL
from
hr.DEPARTMENTS d
inner join hr.EMPLOYEES e
on d.MANAGER_ID = e.EMPLOYEE_ID
order by d.DEPARTMENT_ID;
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALL LAST'));
以下のようにHint Reportの項目が出力されます。
LEADINGとUSE_NLが使用されていることが確認できます。
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 151 (100)| |
| 1 | SORT ORDER BY | | 11 | 528 | 151 (1)| 00:00:01 |
| 2 | NESTED LOOPS | | 11 | 528 | 150 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 2889 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 21 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
1 - SEL$58A6D7F6
- LEADING (e d)
4 - SEL$58A6D7F6 / D@SEL$1
- USE_NL(d)
ヒントレポートは、DBMS_XPLAN.DISPLAY_CURSORのformatに"ALL", "HINT_REPORT", "HINT_REPORT_USED"を指定した場合に表示されます。
指定しなくても、使用されないヒントがある場合は自動で表示されます。
ヒントが使われないとき、構文にエラーがある場合は以下のような表示になります。
ヒントが使われないとき
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
3 - SEL$58A6D7F6 / D@SEL$1
U - USE_NL(d)
構文にエラーがある場合
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
0 - SEL$2
E - USE_NL1
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (N - Unresolved (1))
---------------------------------------------------------------------------
1 - SEL$58A6D7F6
N - USE_NL(d1)
SQLを書き換えずにヒントをつける（SQLパッチ機能）
SQLパッチは、SQL文のコンパイル中または実行中のエラーを回避するためにSQL修復アドバイザによって自動的にヒントが作成されます。
Oracle12cR2からは、CREATE_SQL_PATCHファンクションによって手動でヒントを追加することがっできるようになりました。
これによって、SQLを修正しなくてもヒントを追加することができます。
似たようなことはSPMでもできますが、単にSQLにヒント句をつけるということであればSQLバッチ機能を用いるのが容易です。
CREATE_SQL_PATCHファンクションのオプションは次のようになります。
SQL IDを指定していますが、代わりにSQL文（sql_text）を指定することもできます。
なお、重要な注意点として、ヒント（hint_text）には問合せブロック名をつけて指定する必要があります。
DBMS_SQLDIAG.CREATE_SQL_PATCH(
sql_id=> 'SQL ID',
name => 'SQLパッチ名',
hint_text => 'ヒント'
);
Oracleサンプルスキーマで以下のSQLを実行して、SQLパッチの機能を確認してみます。
select
d.*
, e.FIRST_NAME
, e.LAST_NAME
, e.EMAIL
from
hr.DEPARTMENTS d
inner join hr.EMPLOYEES e
on d.MANAGER_ID = e.EMPLOYEE_ID
order by d.DEPARTMENT_ID;
まず、そのままSQLを実行すると、以下のようにHASH JOINの実行計画になります。
set serveroutput off
set linesize 1000
set pagesize 0
select
d.*
, e.FIRST_NAME
, e.LAST_NAME
, e.EMAIL
from
hr.DEPARTMENTS d
inner join hr.EMPLOYEES e
on d.MANAGER_ID = e.EMPLOYEE_ID
order by d.DEPARTMENT_ID;
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALL LAST'));
※一部省略
SQL_ID 0uh5pm2a3rznm, child number 0
-------------------------------------
select d.* , e.FIRST_NAME , e.LAST_NAME , e.EMAIL from
hr.DEPARTMENTS d inner join hr.EMPLOYEES e on d.MANAGER_ID =
e.EMPLOYEE_ID order by d.DEPARTMENT_ID
Plan hash value: 4213409228
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | SORT ORDER BY | | 11 | 528 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 11 | 528 | 6 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPARTMENTS | 11 | 231 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 2889 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$58A6D7F6
3 - SEL$58A6D7F6 / D@SEL$1
4 - SEL$58A6D7F6 / E@SEL$1
先ほどの実行計画からSQL IDは"0uh5pm2a3rznm"、ブロック名は"SEL$1"ということを確認できます。
これを使用して、CREATE_SQL_PATCHファンクションは以下のように実行します。
ヒント句にはネステッドループ結合となるように"USE_NL"を指定しています。
DECLARE
l_patch_name VARCHAR2(1000);
BEGIN
l_patch_name := DBMS_SQLDIAG.CREATE_SQL_PATCH(sql_id => '0uh5pm2a3rznm', hint_text => 'LEADING (@SEL$1 e d) USE_NL(@SEL$1 d)', name => 'test patch use_nl');
END;
/
CREATE_SQL_PATCHファンクションを実行後に、再度実行計画を取得すると以下のようになりました。
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALL LAST'));
※一部省略
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 151 (100)| |
| 1 | SORT ORDER BY | | 11 | 528 | 151 (1)| 00:00:01 |
| 2 | NESTED LOOPS | | 11 | 528 | 150 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 2889 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 21 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
1 - SEL$58A6D7F6
- LEADING (@SEL$1 e d)
4 - SEL$58A6D7F6 / D@SEL$1
- USE_NL(@SEL$1 d)
Note
-----
- SQL patch "test patch use_nl" used for this statement
今回は19cで実行しているため、「Hint Report」でヒントの使用状況が表示されています。
また、Noteのところを見ると、「SQL patch "test patch use_nl" used for this statement」と表示されており、先ほど作成したSQLパッチが使用されいていることが確認できます。
なお、作成したSQLパッチはDBA_SQL_PATCHESで以下のように確認することができます。
select name, status, force_matching from dba_sql_patches
where name = 'test patch use_nl';
NAME STATUS FOR
-------------------------------------------------------------------------------------------------------------------------------- -------- ---
test patch use_nl ENABLED NO
作成したSQLパッチを削除する場合は以下のように実行します。
BEGIN
DBMS_SQLDIAG.DROP_SQL_PATCH(name => 'test patch use_nl');
END;
/
また、ヒントに「IGNORE_OPTIM_EMBEDDED_HINTS」を指定することで既存のヒントを無効化することもできます。
SQLパッチ機能はSQLを書き換えなくてもヒント句をつけて実行計画を制御できる有効な機能です。
気をつけるのは、SQLパッチを適用したSQLを書き換えた場合です。このとき、SQL IDが変わってしまうのでSQLパッチが適用されたヒントが適用されなくなります。この場合はSQLを書き換える際に忘れずにヒント句をつけてあげる必要があります。
- PL/SQLパッケージおよびタイプ・リファレンス(19c) 155.5.5 CREATE_SQL_PATCHファンクション
- PL/SQLパッケージおよびタイプ・リファレンス(12.2) 155.5.5 CREATE_SQL_PATCHファンクション
その他
今回は紹介しませんでしたが、以下のヒントも比較的利用頻度が高いようです。
- UNNEST/NO_UNNEST(副問合せの本体のネストの制御)
- MERGE/NO_MERGE(問合せ内のビューのマージの制御)
- USE_CONCAT/NO_EXPAND(OR条件の制御)
ネストした副問合せは効果的でない場合が多いため、オプティマイザはネストした副問合せを解除（Subquery Unnesting）することがあります。ただし、ネストした副問合せを解除が逆に誤った実行計画となる場合もあります。
そのような場合に、解除を許可しないようにNO_UNNESTヒントを使用します。
参考：「UNNEST SUBQUERY」