2023.08.22
SQLチューニング（第53回）
「画面PAGINATION処理する時これは守ろう」（7/7回）
今回のSQLチューニングは、「画面PAGINATION処理する時これは守ろう」の最終回です。
「基本的な方式のPAGINATIONを学ぼう」をお送りします。
それでは早速はじめていきましょう。
13.6 基本的な方式のPAGINATIONを学ぼう
以前に紹介した「 ページ処理の際に知っておくべき必須要件 」をもとに、効率的なページ処理を実行することができる
SQLを作成する過程を一緒に見ていきましょう。
このプロセスをきちんと理解することで、ページ処理に関するより深い理解を得られる機会になることを願っています。
以下の記載例は、性能問題があるSQLの影響で、正常なページ処理が行われなかったオンライン画面です。
このケースでのページ処理は、WEBサーバー側が担っているものと仮定した上で話を進めます。
この様な状態にあるSQLは、非効率なプロセスが多く実行されるために性能問題が生じる結果を招きます。
正しいSQLを作成して、効率的なページ処理が実行されるようにしていきましょう。
性能問題があるSQL（例）
SELECT …省略
FROM trd100 a ,
trm100 b ,
trm150 c
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.acpt_dt DESC ,
a. acpt_time DESC ;
b.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 41 0.90 26.17 4718 8666 0 599
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 43 0.90 26.18 4718 8666 0 599
Rows Row Source Operation
---------- ---------------------------------------------------
599 SORT ORDER BY (cr=8666 pr=4718 pw=0 time=26173450 us …)
599 TABLE ACCESS BY INDEX ROWID TRM100 (cr=8666 pr=4718 …)
1199 NESTED LOOPS (cr=8067 pr=4141 pw=0 time=33177311 us …)
599 NESTED LOOPS (cr=6864 pr=3678 pw=0 time=42743190 us …)
2057 TABLE ACCESS BY INDEX ROWID TRD100 (cr=2143 pr=2050 …)
2057 INDEX RANGE SCAN IX_TRD100_01 (cr=163 pr=162 …)
599 TABLE ACCESS BY INDEX ROWID TRM150 (cr=4721 pr=1628 …)
599 INDEX UNIQUE SCAN PK_TRM150 (cr=4122 pr=1059 …)
599 INDEX UNIQUE SCAN IX_TRM100_02 (cr=1203 pr=463 …)
[A] WHERE照会条件
AND a.proc_stus IN ( 'D' , 'E' )
AND a.brof_cd LIKE TRIM( :b0 ) || '%'[B]インデックス構成情報
INDEX_NAME COLUMN LIST
------------ ---------------------------------------
ix_trd100_01 brof_cd, proc_stus, acpt_dt, acpt_time[C] ORDER BY (整列基準)
ORDER BY a.acpt_dt DESC,
a.acpt_time DESC[D] ROWNUM処理 − なし。[E] PLAN上OPERATION
599 SORT ORDER BY (cr=8666 pr=4718 pw=0 time=26173450 us …)
● SQL分析結果
［A］,［B］,［C］,［D］から［E］を分析
分析結果 → 正常なページ処理が行われずに全データを抽出したことで非効率な処理が発生した。
性能問題のあるSQLが、DBサーバーから599件分の全データを抽出したオンライン画面です。
抽出された全データは、SQLがそのままWASサーバーへ伝送をします。
WASサーバーは、受け取った全データの中からクライアント画面に表示するために必要なデータだけを再抽出して
クライアント側へ送信します。この状態の場合、DBサーバーは「効率的なページ処理（部分範囲処理）」は実行しません。
しかし、実際のクライアント画面では 「 ページ処理がされた状態のデータ 」 を見ることができます。
次に、性能問題のあるSQLでページ処理を進めた場合と、正常にページ処理が実行できるSQLを作成した場合で
どの程度の性能差が発生するのか？を分析していきましょう。
誤ったページ処理［1］
ROWNUM処理とORDER BY節を間違って使った場合 （実行計画:SORT ORDER BY -> COUNT）
SELECT *
FROM (
SELECT ROWNUM rnum , x.*
FROM ( SELECT /*+ LEADING(A) USE_NL(A B C) INDEX_DESC(A IX_TRD100_01) */
…省略
FROM trd100 a, trm100 b, trm150 c
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.acpt_dt DESC , a.acpt_time DESC
) x
)
WHERE rnum <= :b2 and rnum >= :b3 ;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 0.90 27.90 4672 8665 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.90 27.91 4672 8665 0 50
Rows Row Source Operation
---------- ---------------------------------------------------
50 FILTER (cr=8665 pr=4672 pw=0 time=27904482 us)
50 VIEW (cr=8665 pr=4672 pw=0 time=27904479 us …)
599 COUNT (cr=8665 pr=4672 pw=0 time=27905514 us)
599 VIEW (cr=8665 pr=4672 pw=0 time=27905065 us …)
599 SORT ORDER BY (cr=8665 pr=4672 pw=0 time=27904910 us …)
599 TABLE ACCESS BY INDEX ROWID TRM100 (cr=8665 pr=4672 …)
1199 NESTED LOOPS (cr=8066 pr=4100 pw=0 time=4626814 us …)
599 NESTED LOOPS (cr=6863 pr=3635 pw=0 time=13468562 us …)
2057 TABLE ACCESS BY INDEX ROWID TRD100 (cr=2142 pr=1905 …)
2057 INDEX RANGE SCAN DESCENDING IX_TRD100_01 (cr=163 pr=1 …)
599 TABLE ACCESS BY INDEX ROWID TRM150 (cr=4721 pr=1730 …)
599 INDEX UNIQUE SCAN PK_TRM150 (cr=4122 pr=1166 …)
599 INDEX UNIQUE SCAN IX_TRM100_02 (cr=1203 pr=465 …)
[A] WHERE照会条件
AND a.proc_stus IN ( 'D' , 'E' )
AND a.brof_cd LIKE TRIM( :b0 ) || '%'[B]インデックス構成情報
INDEX_NAME COLUMN LIST
------------- --------------------------------------
ix_trd100_01 brof_cd, proc_stus, acpt_dt, acpt_time[C] ORDER BY (整列基準)
ORDER BY a.acpt_dt DESC,
a.acpt_time DESC[D] ROWNUM処理
rnum <= :b2 and rnum >= :b3[E] PLAN上OPERATION
599 COUNT (cr=8665 pr=4672 pw=0 time=27905514 us)
599 VIEW (cr=8665 pr=4672 pw=0 time=27905065 us …)
599 SORT ORDER BY (cr=8665 pr=4672 pw=0 time=27904910 us …)
誤ったページ処理［1］の改善前SQL
SELECT *
FROM (
SELECT ROWNUM rnum ,
x.*
FROM (
SELECT* FROM …
ORDER BY a.acpt_dt DESC,
a.acpt_time DESC ---> 問題点.Order By節とインデックス構成順序と違う
) x
)
WHERE rnum <=:b2 AND rnum >=:b3;--->問題点.ROWNUM処理が不適切
誤ったページ処理［1］の改善後SQL
SELECT *
FROM (
SELECT ROWNUM rnum ,
x.*
FROM (
SELECT* FROM …
ORDER BY a.brof_cd DESC,
a.proc_stus DESC,
a.acpt_dt DESC,
a.acpt_time DESC ---> 改善内容.Order By節とインデックス構成順序とオーダーメード
) x
WHERE ROWNUM <=:b2 --->改善内容.部分範囲処理されるようにROWNUM処理変更
)
WHERE rnum >= :b1 ;
誤ったページ処理［2］
ROWNUM処理は効率的やORDER BYを間違って使った場合 （実行計画:SORT ORDER BY STOPKEY -> COUNT STOPKEY）
ROWNUM処理は効率的やORDER BYを間違って使った場合(実行計画:SORT ORDER BY STOPKEY -> COUNT STOPKEY)
SELECT *
FROM (
SELECT ROWNUM rnum , x.*
FROM (
SELECT /*+ LEADING(A) USE_NL(A B C) INDEX_DESC(A IX_TRD100_01) */
…省略
FROM trd100 a ,
trm100 b ,
trm150 c
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.acpt_dt DESC ,
a.acpt_time DESC
) x
WHERE ROWNUM <= :b2
)
WHERE rnum >= :b3 ;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 5 0.97 31.65 4949 8665 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.98 31.66 4949 8665 0 50
Rows Row Source Operation
---------- ---------------------------------------------------
50 VIEW (cr=8665 pr=4949 pw=0 time=31655963 us …)
50 COUNT STOPKEY (cr=8665 pr=4949 pw=0 time=31655952 us)
50 VIEW (cr=8665 pr=4949 pw=0 time=31655949 us …)
50 SORT ORDER BY STOPKEY (cr=8665 pr=4949 pw=0 time=31655944 us …)
599 TABLE ACCESS BY INDEX ROWID TRM100 (cr=8665 pr=4949 …)
1199 NESTED LOOPS (cr=8066 pr=4373 pw=0 time=10498695 us …)
599 NESTED LOOPS (cr=6863 pr=3885 pw=0 time=20250270 us …)
2057 TABLE ACCESS BY INDEX ROWID TRD100 (cr=2142 pr=2108 …)
2057 INDEX RANGE SCAN DESCENDING IX_TRD100_01 (cr=163 pr=162 …)
599 TABLE ACCESS BY INDEX ROWID TRM150 (cr=4721 pr=1777 …)
599 INDEX UNIQUE SCAN PK_TRM150 (cr=4122 pr=1207 …)
599 INDEX UNIQUE SCAN IX_TRM100_02 (cr=1203 pr=488 …)[A] WHERE照会条件
AND a.proc_stus IN ( 'D' , 'E' )
AND a.brof_cd LIKE TRIM( :b0 ) || '%'
[B]インデックス構成情報
INDEX_NAME COLUMN LIST
------------ ---------------------------------------
ix_trd100_01 brof_cd, proc_stus, acpt_dt, acpt_time
[C] ORDER BY (整列基準)
ORDER BY a.acpt_dt DESC,
a.acpt_time DESC
[D] ROWNUM処理
WHERE ROWNUM <= :b2
[E] PLAN上OPERATION
50 COUNT STOPKEY (cr=8665 pr=4949 pw=0 time=31655952 us)
50 VIEW (cr=8665 pr=4949 pw=0 time=31655949 us …)
50 SORT ORDER BY STOPKEY (cr=8665 pr=4949 pw=0 time=31655944 us …)
● SQL分析結果
［A］,［B］,［C］,［D］から［E］を分析
SORT ORDER BY STOPKEY − ORDER BY節義順序がインデックス カラム順序に非適合
COUNT STOPKEY − ROWNUM処理が適合（ 部分範囲処理される方式 ）
誤ったページ処理［2］の改善前SQL
SELECT *
FROM ( SELECT ROWNUM rnum,
X.* FROM (
SELECT* from …
ORDER BY a.acpt_dt desc,
a.acpt_time desc ---> 問題点.Order By節とインデックス構成順序と違う。
) x
WHERE rownum <= :b2
)
WHERE rnum >= :b1 ;
誤ったページ処理［2］の改善後SQL
SELECT *
FROM (
SELECT ROWNUM rnum,
X.*
FROM (
SELET *
FROM …
ORDER BY a.brof_cd desc,
a.proc_stus,
a.acpt_dt desc,
a.acpt_time desc --> 改善内容.Order By節とインデックス構成順序とオーダーメード
) x
WHERE ROWNUM <= :b2
)
WHERE rnum >= :b1 ;
誤ったページ処理［3］
ROWNUM処理が誤った場合(実行計画:COUNT [SORT ORDER BY(X)] )
SELECT *
FROM (
SELECT ROWNUM rnum ,
x.*
FROM (
SELECT /*+ LEADING(A) USE_NL(A B C) INDEX_DESC(A IX_TRD100_01) */
…省略
FROM trd100 a ,
trm100 b ,
trm150 c
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 ;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 0.16 3.95 538 8677 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.17 3.96 538 8677 0 50
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 us …)
599 INDEX UNIQUE SCAN PK_TRM150 (cr=1204 pr=8 pw=0 time=53068 us …)
[A] WHERE照会条件
AND a.proc_stus IN ( 'D' , 'E' )
AND a.brof_cd LIKE TRIM( :b0 ) || '%'
[B]インデックス構成情報
INDEX_NAME COLUMN LIST
------------ ---------------------------------------
ix_trd100_01 brof_cd, proc_stus, acpt_dt, acpt_time
[C] ORDER BY (整列基準)
ORDER BY a.brof_cd DESC,
a.proc_stus DESC,
a.acpt_dt DESC,
a.acpt_time DESC
[D] ROWNUM処理
WHERE rnum <= :b2 and rnum >= :b3
[E] PLAN上OPERATION
599 COUNT (cr=8677 pr=538 pw=0 time=3261348 us)
● SQL分析結果
［A］,［B］,［C］,［D］から［E］を分析
ページ処理結果 → ページ処理が非効率（下のPLANのオペレーションで判断）。
COUNT? PLANにSORT ORDER BY[STOPKEY]なしにCOUNTだけある場合は、ORDER BY節のカラム順序と
インデックス構成情報は効率的でも、ROWNUM処理が誤っている時見えるオペレーションで部分範囲処理されない。
誤ったページ処理［3］の改善前SQL
SELECT *
FROM (
SELECT ROWNUM rnum,
X.*
FROM (
SELECT* FROM …
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処理が不適切
誤ったページ処理［3］の改善後SQL
SELECT *
FROM (
SELECT ROWNUM rnum,
X.*
FROM (
SELECT* FROM …
ORDER BY a.brof_cd desc,
a.proc_stus,
a.acpt_dt desc,
a.acpt_time desc
) x
WHERE ROWNUM <=:b2 --->改善内容.部分範囲処理されるようにROWNUM処理変更
)
WHERE rnum >= :b3 ;
［ 改善後SQL ］
SELECT *
FROM (
SELECT ROWNUM rnum ,
x.*
FROM ( SELECT /*+ leading(a) use_nl(a b c) index_desc(a ix_trd100_01) */
…省略
FROM trd100 a ,
trm100 b ,
trm150 c
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 ROWNUM <= :b2
)
WHERE rnum >= :b3 ;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 0.07 2.23 358 658 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.07 2.23 358 658 0 50
Rows Row Source Operation
---------- ---------------------------------------------------
50 VIEW (cr=658 pr=358 pw=0 time=94414 us cost=9 size=206 card=1)
50 COUNT STOPKEY (cr=658 pr=358 pw=0 time=94403 us)
50 VIEW (cr=658 pr=358 pw=0 time=94370 us cost=9 size=193 card=1)
50 TABLE ACCESS BY INDEX ROWID TRM150(cr=658 pr=358 pw=0 time=94360 us …)
122 NESTED LOOPS (cr=608 pr=302 pw=0 time=10541661 us …)
61 NESTED LOOPS (cr=480 pr=264 pw=0 time=1770693 us …)
136 TABLE ACCESS BY INDEX ROWID TRD100 (cr=140 pr=128 …)
136 INDEX RANGE SCAN DESCENDING IX_TRD100_01 (cr=8 pr=3 …)
61 TABLE ACCESS BY INDEX ROWID TRM100 (cr=340 pr=136 …)
61 INDEX UNIQUE SCAN IX_TRM100_02(cr=279 pr=80 pw=0 time=466051 us …)
61 INDEX UNIQUE SCAN PK_TRM150 (cr=128 pr=38 pw=0 time=295945 us …)
[A] WHERE照会条件
AND a.proc_stus IN ( 'D' , 'E' )
AND a.brof_cd LIKE TRIM( :b0 ) || '%'
[B]インデックス構成情報
INDEX_NAME COLUMN LIST
------------ --------------------------------------
ix_trd100_01 brof_cd, proc_stus, acpt_dt, acpt_time
[C] ORDER BY (整列基準)
ORDER BY a.brof_cd DESC,
a.proc_stus DESC,
a.acpt_dt DESC,
a.acpt_time DESC
[D] ROWNUM処理
WHERE ROWNUM <= :b2
[E] PLAN上OPERATION
50 COUNT STOPKEY (cr=658 pr=358 pw=0 time=94403 us)
● SQL分析結果
［A］,［B］,［C］,［D］から［E］を分析
ページ処理結果 → 効率的なページ処理が実行された
Count STOPKEY PLANに「SORT ORDER BY [ STOPKEY ]」が無い状態で、COUNT STOPKEYだけがある場合、 ROWNUM処理とORDER BYの構成などが皆適合して効率的なページが処理される場合がある。
● まとめ
今回のSQLチューニングでは、性能問題のあるSQLが部分範囲処理が実行できるようになり、効率的なページ処理が
行われるようにSQLの作成をする方法を調べてきました。
ROWNUM処理、Where節条件、Order By節、そしてインデックス構成は、効率的なページ処理における必須要件です。
そして、Where節とOrder By節を実行するテーブルはDriving Tableでなければなりません。
又、結合させる場合には、Nested Loops Joinで実行する必要があります。
ここまでの内容を読んだ読者の方には、もしかするとページ処理は非常に簡単ではないか？と考える方もいるかと思います。
しかし、ページ処理の基本と核心を完ぺきに理解せずにいると、誤ったページ処理をSQLに適用させてしまいます。
その結果、とんでもないデータが出力されたり、予期せぬ非効率なプロセスを発生させる原因となり、DBサーバーに性能問題を
引き起こす可能性が出てきますので注意しましょう。
さらにページ処理は決して簡単に理解できる内容ではありません。
几帳面で深みある理解を基本としてSQLを作成する必要があると思います。
SQLチューニングブログ（第53回） 終
今回の Oracle SQL チューニングブログ はいかがでしたか？
全53回でお届けしてきました「SQLチューニングブログ」は今回で終了となります。
ご愛読いただきまして誠にありがとうございました。
次回ブログテーマ
新シリーズがスタート！
「 SQLチューニング 2nd シーズン 」
次回もどうぞお楽しみに
それでは See you next time！
