2023.06.21
SQLチューニング(第50回)
「画面PAGINATION処理する時これは守ろう」(4/7回)
今回のOracle SQLチューニングは、「 画面PAGINATION処理する時これは守ろう 」の第4回目です。
「 PLANに現れるオペレーション中、COUNTに騙されないでおこう 」と題してお送りします。
それでは早速はじめましょう。
13.3 PLANに現れるオペレーション中、COUNTに騙されないでおこう
部分範囲処理がされるように誘導するページ処理は、適切なROWNUM処理とOrder By節、Where節、インデックス構成情報が
適切であり、テーブル間結合方式がNested Loops Joinで実行されるのであれば効率的な実行が可能です。
“ 13.1 ROWNUM処理をよくしよう ” で説明した 部分範囲処理が不可能な例 の場合と共に適切でないROWNUM処理をしても
Where節、インデックス構成情報とOrder By節の整列基準が合えば、SORT ORDER BYオペレーションが現れずに、COUNT
オペレーションが見えてきます。 COUNTオペレーションがあたかも全体データ処理をせずに実行されているように見えますが
実際には、全体データを全部処理した後に ROWNUM を実行することになるので注意しなければなりません。
以下の事例を通じて確認してみましょう。
...(省略)
WHERE a.acpt_no = b.acpt_no
AND a.acpt_no = c.acpt_no
AND b.styl_cd = 'AC01'
AND a.proc_stus IN ( 'D' , 'E' )
AND a.brof_cd LIKE TRIM( :b0 ) || '%'
AND a.acpt_dt || a.acpt_time < :b1
ORDER BY a.brof_cd DESC,--->インデックス カラム構成順序とマッチングになること
a.proc_stus DESC,
a.acpt_dt DESC,
a.acpt_time DESC
) x
WHERE rnum <=:b2 AND rnum >=:b3 ---> ROWNUM処理が非効率
Rows Row Source Operation
---------- ---------------------------------------------------
50 FILTER (cr=8677 pr=538 pw=0 time=1957 us)
50 VIEW (cr=8677 pr=538 pw=0 time=1955 us cost=9 size=159 card=1)
599 COUNT (cr=8677 pr=538 pw=0 time=3261348 us) --->全体データ処理する。
599 VIEW (cr=8677 pr=538 pw=0 time=3260896 us cost=9 size=146 card=1)
599 TABLE ACCESS BY INDEX ROWID TRM150 (cr=8677 pr=538 …)
1199 NESTED LOOPS (cr=8078 pr=538 pw=0 time=227759 us …)
599 NESTED LOOPS (cr=6874 pr=530 pw=0 time=4307258 us …)
2057 TABLE ACCESS BY INDEX ROWID TRD100 (cr=2147 pr=10 …)
2057 INDEX RANGE SCAN DESCENDING IX_TRD100_01 (cr=167 pr=0 …)
599 TABLE ACCESS BY INDEX ROWID TRM100 (cr=4727 pr=520 …)
599 INDEX UNIQUE SCAN IX_TRM100_02 (cr=4128 pr=7 pw=0 time=56539 …)
上記トレース結果を見ればROWNUM条件で範囲を問い合わせて非効率的に処理されています。
しかし、Where節のインデックス構成情報とOrder By節が適切に構成されている SORT ORDER BYオペレーションではない
COUNTオペレーションがあるのを確認することができます。
この時、COUNTはCOUNT STOPKEYとは違うように全体データを全部処理するので非効率的に実行されます。
これを効率的なページ処理で実行されるようにするためには、先立って“ ROWNUM処理をよくしよう ” で説明したように
RNUMを置き換える前に:B3値に該当するデータだけを持ってくるようにROWNUMに条件を追加する必要があります。
SQLチューニングブログ(第50回) 終
今回の Oracle SQLチューニングブログ はいかがでしたか?
次回のブログは「画面PAGINATION処理する時これは守ろう 」の5回目です。
次回ブログテーマ
「 NESTED LOOPS JOINを使おう 」