2024.10.22
SQLチューニング 2nd Season（第21回） 第4章 SQL TuningとHINTの関係 （4/8）
今回のSQLチューニングブログ2nd Season（第21回）は「 SQL TuningとHINTの関係 」の第4回目です。
「VIEWコントロールヒント・サブクエリ制御のヒントEXPANSION 制御ヒント」について解説していきます。
それでは早速、はじめていきましょう！！
4.2.4 VIEWコントロールヒント
View（以下ビュー）は、次のサブクエリと一緒にオプティマイザが実行計画を生成する時、誤った解釈をする可能性が高い部分です。
なぜなら、ビューやサブクエリは別のQuery Blockで数が多くなると、オプティマイザのコスト計算自体がより複雑になり、非効率的な
実行計画を立てることが多いからです。
ビューやサブクエリは似たような2つの動作方式を持っています。
1つ目に、ビューやサブクエリを独立して実行した後、Main Queryと結合される方式です。
2つ目として、ビューとサブクエリがMain Queryと一緒に実行しながらデータを処理する方式です。
ビューを制御するヒントはMERGEとNO_MERGEがありますが、ビューによるSQL性能問題は、VIEW MERGINGによる問題が多くなる
ので、VIEW制御のヒントとなるNO_MERGEについて説明していきます。
・NO_MERGE
使用バージョン：8.0.0
使用方法: /*+ NO_MERGE */ (Inverse: MERGE)
ヒント意味: インラインビューやビューをビュー外部テーブルとMergingされず、独立して実行したい時に使われるヒント。
NO_MERGEヒントを多く使う場合は以下のような場合です。
t2.cost
from t1,
( select ---> 抽出件数 : 100件(グループ化後)
id,
sum(cost) as cost
from t2
group by id
) t2
where t1.id = t2.id ;
説明のために作成した上のSQLを使ってNO_MERGEヒントを使う必要がある状況を説明します。
上のSQLの実行はジョイン順序：T2 -> T1, ジョイン方法: Nested Loops Joinで実行しますが、インラインビューT2はGroup Byを実行した後、100件抽出し、この後、T1テーブルはT2.IDカラムに生成されたインデックスで100回実行した後、最終データを抽出しなければ性能上問題がないと思われます。
ところが、もしインラインビューT2がT1とVIEW MERGINGになると、T2構文内のGROUP BY節がT1テーブルとジョインを実行した後、
最終データ抽出時に処理され、性能問題を引き起こす可能性があります。
このような場合、インラインビューT2にNO_MERGEヒントを追加して、パフォーマンスの問題を取り除くことができます。
下記の使用例のようにNO_MERGEヒントを使用すると、実行計画にVIEWオペレーションがあります。
使用例：
SELECT T1.*
FROM HINT_T3 T1 ,
(
SELECT /*+ NO_MERGE */
*
FROM HINT_T2
WHERE ADDR_DIV = 0
) T2
WHERE T1.CUST_NO = T2.CUST_NO
AND T1.ORDDATE BETWEEN TO_DATE('2013-01-01','YYYY-MM-DD')
AND TO_DATE('2013-01-31','YYYY-MM-DD');
--------------------------------------------------------------------------------------
| Id | Operation | Name | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |00:00:02.29 | 976 | 968 |
|* 1 | HASH JOIN | |00:00:02.29 | 976 | 968 |
| 2 | TABLE ACCESS BY INDEX ROWID| HINT_T3 |00:00:00.01 | 4 | 0 |
|* 3 | INDEX RANGE SCAN | IDX03_HINT_T3 |00:00:00.01 | 3 | 0 |
| 4 | VIEW | |00:00:01.16 | 972 | 968 |
|* 5 | TABLE ACCESS FULL | HINT_T2 |00:00:00.41 | 972 | 968 |
--------------------------------------------------------------------------------------
** 実行計画上VIEWオペレーションがない場合はVIEW MERGINGが発生した場合である。4.2.5 サブクエリ制御のヒント
サブクエリとは、WHERE節に比較条件として使われるSELECT文(Query Block)を意味します。
一般的に、SQLを作成する時、サブクエリを使用して作成する方がFROM節においてジョインで作成するよりも簡単です。
しかし、サブクエリをジョイン形式で作成できるにもかかわらず、サブクエリを多用すると、DBサーバーに予期せぬ深刻な性能問題を
引き起こす可能性があります。
オプティマイザは、SQL内に複数のQuery Block（インラインビュー、サブクエリ）が含まれている場合、実行計画のコスト計算時に
エラーを犯す確率が高くなります。
そして、サブクエリの動作方式は大きくFilter動作方式とジョイン動作方式に分けることができます。
どの動作方式で処理されるかによって、SQLの性能差が大きく発生することがあります。
したがって、サブクエリが効率的に実行できるように動作方式を理解して制御する必要があります。
Filterの動作方式は、Main SQLから抽出されたデータ数だけサブクエリが繰り返し実行されて処理される方式です。
つまり、Main SQLの抽出結果に対して、各ローごとにサブクエリにジョイン接続値(以下、Input値)を提供した後、実行した結果が”TRUE”の場合、データを抽出します。
例えば、Main SQLの抽出結果が100万件であれば、サブクエリは最大100万回実行されます。
ところが、もし、上記のようなSQLでサブクエリのジョイン接続カラムに適切なインデックスがなければどうなるでしょうか？
みなさんも予想できるように、サブクエリを100万回繰り返しFull Table Scanを実行することになるでしょう。
このような実行計画を持つSQLが最悪の性能を示すことはもちろん、DBサーバーの性能にも深刻な悪影響を及ぼすでしょう。
一方、Main SQLの抽出件数が2件の場合、Filter動作方式で処理すれば、サブクエリは最大2回だけ実行すればよく、さらには、サブクエリのジョイン接続カラムで構成されたUnique Indexが存在すれば、非常に効率的な処理ができるでしょう。
しかし、Main SQLの抽出結果が多い場合、Filter動作方式で処理されると、常に性能が悪いのか？という疑問が生じることがあります。
この事について結論から言うと、いつもそうではありません。
Main SQLの抽出結果が多くても、サブクエリのInput値が全て同じであれば、まるでMain SQLで1件だけ抽出したように1回だけ実行するため、性能問題が発生しません。
これは、Oracleが内部的にFilterの動作方式に対してFilter Optimization（サブクエリを実行するInput値をCacheし、同じInput値が使用される場合、追加の読み取り作業なしにCacheされた値を使用する）と呼ばれる最適化作業を行うためです。
しかし、一般的にサブクエリのInput値が全て同じ値である確率は非常に低いため、抽出件数が多い場合、サブクエリをFilter動作方式で処理する場合、性能上非効率的な場合が多くなります。
したがって、Filter動作方式で実行される場合、Input値の種類が少なく、性能に有利かどうかを必ず確認する必要があります。
ジョイン動作方式はサブクエリをFROM節テーブルのようにジョインで実行することを言います。
そして、ジョイン動作方式をFilter動作方式と比較した時、一番大きな違いは可変性だと思います。
先ほど説明したFilter動作方式は実行順序や実行方法が固定されていて、様々な状況に柔軟に対応することが難しい一方、ジョイン動作方式はNested Loops Join, Hash Join, Sort Merge Join, Semi Join, Anti Joinなどの様々なジョイン方法の中から有利なものを選択することができ、Semi / Anti Joinを除いて実行順序まで選択することができるため、より柔軟な処理が可能です。
しかし、ジョイン動作方式の中でNested Loops Join Semiを除いた残りのジョイン方法は、Filter動作方式が持っているFILTERオペレーション効果による利点を得ることができません。
したがって、Input値の種類が少ない場合は、むしろFilter動作方式が性能上有利な場合もあります。
・UNNEST
使用バージョン：8.1.6〜
使用方法: /*+ UNNEST */ (Inverse: NO_UNNEST)
ヒント意味: サブクエリをジョイン動作方式で処理するために使用されるヒント。
FILTER動作方式を選択せずにジョイン動作方式で処理したい場合、サブクエリにUNNESTヒントを使用します。
つまり、サブクエリをFROM節のテーブルとジョインするように誘導するヒントです。
主にFROM節のテーブルから抽出されるデータ件数が多い場合や、FROM節のテーブルと結合されるサブクエリの
カラムにインデックスが存在しない場合に使用されます。
使用例：
SELECT T1.
FROM HINT_T3 T1
WHERE T1.ORDDATE BETWEEN TO_DATE( '2013-01-01' , 'YYYY-MM-DD' )
AND TO_DATE( '2013-01-31' , 'YYYY-MM-DD' )
AND EXISTS (
SELECT /*+ UNNEST */
'X'
FROM HINT_T2 T2
WHERE ADDR_DIV = 0
AND T1.CUST_NO = T2.CUST_NO
) ；
-----------------------------------------------------------------------------------------------
| ID｜操作｜名前｜E-Rows｜A-Rows｜A-Time｜Buffers｜バッファー
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 00:00:00:00.01 | 116 | 116
| 1 | NESTED LOOPS SEMI | | 31 | 12 |00:00:00:00.01 | 116 | NESTED LOOPS SEMI
| 2 | TABLE ACCESS BY INDEX ROWID| HINT_T3 | 31 | 31 | 00:00:00:00.01 | 4 | TABLE ACCESS BY INDEX ROWID| HINT_T3 | 31 | 31 | 00:00:00.01 | 4 | HINT_T4
|* 3 | INDEX RANGE SCAN | IDX03_HINT_T3 | 31 | 31 | 00:00:00:00.01 | 3 | IDX03_HINT_T3
|* 4 | TABLE ACCESS BY INDEX ROWID | HINT_T2 | 150K| 12 | 00:00:00.01 | 112 |
|* 5 | INDEX RANGE SCAN | IDX02_HINT_T2 | 2 | 60 | 00:00:00:00.01 | 64
-----------------------------------------------------------------------------------------------
・NO_UNNEST
使用バージョン：8.1.6〜
使用方法: /*+ NO_UNNEST */ (Inverse: UNNEST)
ヒント意味: サブクエリをFilter動作方式で処理するために使用されるヒント。
サブクエリをFILTER動作方式で処理したい場合、サブクエリにNO_UNNESTヒントを使用します。
FROM節テーブルから抽出したデータ数だけサブクエリは繰り返し実行されるので、FROM節テーブルと結合される
サブクエリのカラムに必ず効率的なインデックスがなければなりません。
使用例：
SELECT T1.
FROM HINT_T3 T1
WHERE T1.ORDDATE BETWEEN TO_DATE( '2013-01-01' , 'YYYY-MM-DD' )
AND TO_DATE( '2013-01-31' , 'YYYY-MM-DD' )
AND EXISTS (
SELECT /*+ NO_UNNEST */ /*+ NO_UNNEST
'X'
FROM HINT_T2 T2
WHERE ADDR_DIV = 0
AND T1.CUST_NO = T2.CUST_NO
) ；
-----------------------------------------------------------------------------------------------
| ID｜操作｜名前｜E-Rows｜A-Rows｜A-Time｜Buffers｜A-Time｜バッファ
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 00:00:00:00.01 | 00:00:00.01 | 157
|* 1 | FILTER | | 12 | 00:00:00:00.01 | 157 |
| 2 | TABLE ACCESS BY INDEX ROWID| HINT_T3 | 31 | 31 | 00:00:00:00.01 | 4 | TABLE ACCESS BY INDEX ROWID| HINT_T3 | 31 | 31 | 00:00:00.01 | 4 | HINT_T4
|* 3 | INDEX RANGE SCAN | IDX03_HINT_T3 | 31 | 31 | 00:00:00:00.01 | 3 | IDX03_HINT_T3
|* 4 | TABLE ACCESS BY INDEX ROWID| HINT_T2 | 1 | 12 | 00:00:00.01 | 153 |
|* 5 | INDEX RANGE SCAN | IDX02_HINT_T2 | 3 | 60 | 00:00:00:00.01 | 93
-----------------------------------------------------------------------------------------------
・HASH_SJ
使用バージョン：8.1.0〜
使用方法: /*+ HASH_SJ */ /*+ HASH_SJ */
ヒント意味：サブクエリをHash Semi Joinで実行するように誘導するヒント
EXISTSやIN条件を使用した場合、サブクエリにUNNESTと一緒にHASH_SJヒントを付与した場合、HASH JOIN SEMIで処理するように制御することができます。
使用例：
SELECT T1.
FROM HINT_T3 T1
WHERE T1.ORDDATE BETWEEN TO_DATE( '2013-01-01' , 'YYYY-MM-DD' )
AND TO_DATE( '2013-01-31' , 'YYYY-MM-DD' )
AND EXISTS (
SELECT /*+ UNNEST HASH_SJ */ SELECT /*+ UNNEST HASH_SJ */
'X'
FROM HINT_T2 T2
WHERE ADDR_DIV = 0
AND T1.CUST_NO = T2.CUST_NO
) ；
-----------------------------------------------------------------------------------------------
| ID｜操作｜名前｜A-Rows｜A-Time｜バッファ｜読み取り｜読み取り｜読み出し
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 |00:00:01.54 | 975 | 968 | 975 | 968
|* 1 | HASH JOIN SEMI | | 12 |00:00:01.54 | 975 | 968 | HASH JOIN SEMI
| 2 | TABLE ACCESS BY INDEX ROWID| HINT_T3 | 31 | 00:00:00:00.01 | 4 | 0 | HINT_T3
|* 3 | INDEX RANGE SCAN | IDX03_HINT_T3 | 31 | 00:00:00:00.01 | 3 | 0 | 0 | 0
|* 4 | TABLE ACCESS FULL | HINT_T2 | 150K|00:00:00:00.39 | 971 | 968 |
-----------------------------------------------------------------------------------------------
・HASH_AJ
使用バージョン：8.1.0〜
使用方法: /*+ HASH_AJ */ /*+ HASH_AJ */
ヒント意味：サブクエリをHash Anti Joinで実行されるように誘導するヒント
NOT EXISTSやNOT IN条件を使用した場合、サブクエリにUNNESTと一緒にHASH_AJヒントを使用すると、HASH JOIN ANTIで処理するように制御することができます。
Main Queryのテーブルから多くの件数が抽出されますが、サブクエリがFILTER動作方式で実行されると、SQLの性能問題が発生することがあります。
このような場合、HASH_AJヒントを使用して性能改善ができます。
使用例：
SELECT T1.
FROM HINT_T3 T1
WHERE T1.ORDDATE BETWEEN TO_DATE( '2013-01-01' , 'YYYY-MM-DD' )
AND TO_DATE( '2013-01-31' , 'YYYY-MM-DD' )
AND NOT EXISTS (
SELECT /*+ UNNEST HASH_AJ */ SELECT /*+ UNNEST HASH_AJ */
'X'
FROM HINT_T2 T2
WHERE ADDR_DIV = 0
AND T1.CUST_NO = T2.CUST_NO
) ；
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 |00:00:01.51 | 975 | 968 | 975 | 968
|* 1 | HASH JOIN ANTI | | 19 |00:00:01.51 | 975 | 968 | HASH JOIN ANTI
| 2 | TABLE ACCESS BY INDEX ROWID| HINT_T3 | 31 | 00:00:00:00.01 | 4 | 0 | HINT_T3
|* 3 | INDEX RANGE SCAN | IDX03_HINT_T3 | 31 | 00:00:00:00.01 | 3 | 0 | 0 | 0
|* 4 | TABLE ACCESS FULL | HINT_T2 | 150K|00:00:00:00.39 | 971 | 968 |
-----------------------------------------------------------------------------------------------
4.2.6 EXPANSION 制御ヒント
Where節に使われたOR条件は、SQLを分岐して実行する条件として活用することができます。
Where節に「条件1 or 条件2」の形で条件があり、条件1と条件2カラムにそれぞれ効率的なインデックスが生成されている場合、OR構文を条件1,2で別途実行した後、CONCATENATIONして効率的な照会が可能です。
もし、OR構文で分岐された実行計画に非効率が多い場合、逆にCONCATENATIONにならないようにSQLを実行する
必要があります。
上記のような場合に使うヒントが USE_CONCAT と NO_EXPAND です。
各ヒントについてどのように使うのか説明します。
・USE_CONCAT
使用バージョン：8.1.0〜
使用方法: /*+ USE_CONCAT */ (Inverse: NO_EXPAND)
ヒント意味：OR節をExpansion(UNION ALL)で行うように誘導するヒント
下記の例は、USE_CONCATヒントを適用してOR条件に合う各インデックスを使用して実行後、値はCONCATENATIONした実行計画です。
使用例：
SELECT /*+ USE_CONCAT(T3) */
COUNT( * )
FROM HINT_T3 T3
WHERE ( ORD_NO BETWEEN 1 AND 100
OR ORDDATE BETWEEN TO_DATE( '2013-01-01' , 'YYYY-MM-DD' )
AND TO_DATE( '2013-01-31' , 'YYYY-MM-DD' ) );
------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 |00:00:00.01 | 7 |
| 1 | SORT AGGREGATE | | 1 | 1 |00:00:00.01 | 7 |
| 2 | CONCATENATION | | | 100 |00:00:00.01 | 7 |
| 3 | TABLE ACCESS BY INDEX ROWID| HINT_T3 | 31 | 31 |00:00:00.01 | 4 |
|* 4 | INDEX RANGE SCAN | IDX03_HINT_T3 | 31 | 31 |00:00:00.01 | 3 |
|* 5 | INDEX RANGE SCAN | IDX01_HINT_T3 | 100 | 69 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------------------
・NO_EXPAND
使用バージョン：8.1.0〜
使用方法: /*+ NO_EXPAND */ (Inverse: USE_CONCAT)
ヒント意味：OR節をExpansionで行わないように誘導するヒント
下記の例は、NO_EXPANDヒントを適用してSQL実行計画の分岐ができないように誘導しました。
このような場合、通常、照会テーブルはTABLE FULL SCANで実行されます。
しかし、TABLE FULL SCANはテーブル全体のデータを照会しなければならないため、性能問題が発生する可能性が
あり、オプティマイザはWHERE節2つの条件にそれぞれインデックスが生成されている B*Tree Index Combination で実行するように実行計画を生成しました。
使用例：
SELECT /*+ NO_EXPAND */
COUNT( * )
FROM HINT_T3 T3
WHERE ( ORD_NO BETWEEN 1 AND 100
OR ORDDATE BETWEEN TO_DATE( '2013-01-01' , 'YYYY-MM-DD' )
AND TO_DATE( '2013-01-31' , 'YYYY-MM-DD' ) );
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 |00:00:00.01 | 6 |
| 1 | SORT AGGREGATE | | 1 | 1 |00:00:00.01 | 6 |
| 2 | BITMAP CONVERSION COUNT | | 131 | 1 |00:00:00.01 | 6 |
| 3 | BITMAP OR | | | 1 |00:00:00.01 | 6 |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | 1 |00:00:00.01 | 3 |
| 5 | SORT ORDER BY | | | 100 |00:00:00.01 | 3 |
|* 6 | INDEX RANGE SCAN | IDX01_HINT_T3 | 131 | 100 |00:00:00.01 | 3 |
| 7 | BITMAP CONVERSION FROM ROWIDS| | | 1 |00:00:00.01 | 3 |
| 8 | SORT ORDER BY | | | 31 |00:00:00.01 | 3 |
|* 9 | INDEX RANGE SCAN | IDX03_HINT_T3 | 131 | 31 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------------
SQLチューニング 2nd Season（第21回） 終
次回のSQLチューニングブログは
SQLチューニングブログ 2nd Season（第4章）
「SQL Tuning と HINTの関係」（5/8） HINTの種類と使い方（第4回）
～ WITH節の制御ヒント・PARALLELコントロールヒント ～
日本エクセムのデータベースソリューション
データベースの可観測性ソリューション
プロアクティブで高品質なリモートDBA
最新情報は公式SNSでも配信中
私たちはITインフラにおける
プロジェクト運営 ～ システム運用 の安定化と効率化を推進します。
SQLチューニングブログについてのお問い合わせは
