2023.06.08
SQLチューニング（第48回）
「 画面PAGINATION処理する時これは守ろう 」（2／7回）
今回のOracle SQLチューニングは、「 画面PAGINATION処理する時これは守ろう 」の第2回目です。
「 画面PAGINATION処理する時これは守ろう 」の各論に入っていきたいと思います。
それでは早速はじめていきましょう。
13.1 ROWNUM処理を良くしよう
ページ処理を実施するのに先立ち、部分範囲処理が可能であるか？は、ROWNUMをどのように処理するか？によって
決定される場合が多い為、ROWNUMの効率的な使用方法を先にきちんと理解しておく必要があります。
| ◆ 部分範囲処理が可能な例 |
| SELECT * FROM ( SELECT ROWNUM rnum , x.* FROM ( select * from … order by … ) x WHERE ROWNUM <= :b2 ) WHERE rnum >= :b1 ; |
前のSQLにROWNUM <=:B2部分はOptimizerにデータをどこまで抽出して止まらなければならないかを教える部分です。
すなわち、COUNT STOPKEYオペレーション によってOrder By節に記述されたカラム順に整列したデータを:B2に入力
された値だけに抽出して終了します。
例えば、:B2値を100とした場合、全体データのうち整列順序によって100件を抽出するとデータ抽出は止まります。
SQLから抽出される多くのデータから、一部データだけを素早く抽出するための条件に使用できるのがROWNUMです。
従って、ページ処理時に部分範囲処理を可能にするSQLを作成するための必須要件の1つがまさにROWNUMなのです。
但し、ROWNUMは、部分範囲処理となるように使用しなければ、全体範囲処理の中で行われることになります。
その場合にはSQLの性能改善には役立ちません。
| ◆ 部分範囲処理が不可能例 |
SQL[1] SELECT * FROM ( SELECT ROWNUM rnum , x.* FROM ( select * from … order by … ) x ) WHERE rnum >= :b1 AND rnum <= :b2 ; SQL[2] SELECT * FROM ( SELECT ROWNUM rnum , x.* FROM ( select * from … order by … ) x ) WHERE rnum BETWEEN :b1 AND :b2 ; |
SQL[1],SQL[2]の場合、全部の全体データを問い合わせて整列させた後、全体データの中から:B1 ～:B2にマッチング
されるデータを抽出します。全体データ処理量が多いとこのようなページ処理は性能問題を起こす原因になります。
但し、クライアント画面に見せる1ページに必要なデータだけを抽出してDBサーバにWASサーバから伝送することになり
ネットワーク負荷やWASサーバの物理メモリーの使用量を減少させることができる長所はあります。
ここまでは、ページ処理時に効率的なSQL作成を行うための必須要件である ROWNUM処理 について調べてきました。
これで理論上における性能は、ある程度予測することができました。
ここからは テストを通じて ROWNUM処理 に伴う SQLの性能差 について調べていきましょう。
Script. テスト データ生成用
DROP TABLE T1 PURGE;
< T1 >
■ 生成要件
-テーブル データ件数は1,000,000ロー
-カラムc1は値の種類が1,000,000種類。 Unique性格のカラム
■ テーブル生成
create table t1
as
SELECT 1000000-(LEVEL-1) c1, TO_CHAR(SYSDATE-(LEVEL-1),'yyyymmdd') c2, LEVEL c3
from DUAL
connect by level <= 1000000 ;
■ 各カラムにインデックス生成および統計情報収集
CREATE INDEX idx_t1_01 ON t1(c2,c1);
begin
dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',
TABNAME=>'T1',
ESTIMATE_PERCENT=>99,
METHOD_OPT=>'FOR ALL INDEXED COLUMNS',
GRANULARITY=>'ALL', CASCADE=>TRUE,
NO_INVALIDATE=>FALSE) ;
end;
/
次に、SQL作成時にROWNUM処理をどのように変更すれば効率的な実行になるのか？を調べます。
| ◆ 抽出条件 |
T1テーブルでC2 (日データ)カラムの条件によって、今日以前に存在する全部のデータを画面に出力しなければならない。 しかし、今日以前のデータを全部抽出すると、データ件数がとても多くなってしまった為、ページ処理を行った。 この時、各ページにC2,C1カラムを来臨差順で整列したデータを50件ずつ見せなければならない。 |
上記の抽出条件を満たすSQLをここで作成してみましょう。
今日以前のデータを全部抽出後、C2,C1カラムで整列するSQLを作成すると下記の通りになります。
select *
from t1
where c2 <= to_char(sysdate,'yyyymmdd')
order by c2 desc, c1 desc ;
ここで重要なのが、SQLで抽出される全データのうち、選択されたページに該当する50件だけ持ってくるように
ROWNUM条件を追加する必要があることです｡
下のテスト[1] ～ [3]では、同じデータを抽出するSQLであるものの、ROWNUMを処理する方法がそれぞれ違います。
この３個のSQLを通じて、ROWNUM処理の方法によりSQL性能がどのように変化するのか？を確認してみましょう。
テスト[1]. 部分範囲処理されない不適切なROWNUM処理適用SQL (C2+ C1巡で結合インデックスIDX_T1_01が存在する)
SELECT *
FROM (
SELECT ROWNUM rnum,
x.*
FROM (
SELECT
*
FROM t1
WHERE c2 <= TO_CHAR(SYSDATE,'yyyymmdd')
order by c2 desc, c1 desc
) x
)
WHERE rnum >= :b1 AND rnum <= :b2
call cou 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.76 0.77 0 535987 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.78 0.77 0 535987 0 50
Rows Row Source Operation
------- ---------------------------------------------------
50 FILTER (cr=535987 pr=0 pw=0 time=50 us)
50 VIEW (cr=535987 pr=0 pw=0 time=48 us)
1000000 COUNT (cr=535987 pr=0 pw=0 time=44 us)
1000000 VIEW (cr=535987 pr=0 pw=0 time=42 us)
1000000 TABLE ACCESS BY INDEX ROWID T1 (cr=535987 pr=0 pw=0 time=41 us)
1000000 INDEX RANGE SCAN DESCENDING IX_T1_2 (cr=3489 pr=0 pw=0 time=33 us)
Note. Order By節順序と同じインデックスが存在している。
上記トレース結果を見ればわかる通り、全データを処理後に改めて対象となる50件のデータを抽出している。
テスト[2]. 部分範囲処理されない不適切なROWNUM処理適用SQL
SELECT *
FROM (
SELECT ROWNUM rnum ,
x.*
FROM (
SELECT /*+ index_desc(t1(c2)) */
*
FROM t1
WHERE c2 <= TO_CHAR(SYSDATE,'yyyymmdd')
order by c2 desc, c1 desc
) x
)
WHERE rnum BETWEEN :b1 AND :b2
call cou 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.76 0.77 0 535987 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.78 0.77 0 535987 0 50
Rows Row Source Operation
------- ---------------------------------------------------
50 FILTER (cr=535987 pr=0 pw=0 time=50 us)
50 VIEW (cr=535987 pr=0 pw=0 time=48 us)
1000000 COUNT (cr=535987 pr=0 pw=0 time=44 us)
1000000 VIEW (cr=535987 pr=0 pw=0 time=42 us)
1000000 TABLE ACCESS BY INDEX ROWID T1 (cr=535987 pr=0 pw=0 time=41 us)
1000000 INDEX RANGE SCAN DESCENDING IX_T1_2 (cr=3489 pr=0 pw=0 time=33 us)
Note.インデックスを来臨差順で読めとのヒントがなくてこうしたことが発生したことではないかといってヒントを付与してROWNUM条件をBETWEENで作成してSQLを実行しましたが、結果はテスト[1]と同一となりました。
どこに問題があったのでしょうか? その原因は［ ROWNUM条件を間違って作成した ］ことです。
原因についての詳しい説明内容は”部分範囲処理が不可能な例”を参照してみてください。テスト[3]. 部分範囲処理するように適切なROWNUM処理を適用したSQL (C2+ C1巡で結合インデックスidx_t1_01が存在する)
var b1 number
var b2 number
exec :b1 := 1
exec:b2:= 50 ---> 50件
SELECT *
FROM (
SELECT ROWNUM rnum,
x.*
FROM ( SELECT /*+ INDEX_DESC(C2) */ *
FROM T1
WHERE c2 <= TO_CHAR(SYSDATE,'yyyymmdd')
order by c2 desc, c1 desc
) x
WHERE ROWNUM <= :b2
)
WHERE rnum >= :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.05 0 0 0 0
Fetch 5 0.00 0.00 0 12 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.05 0 12 0 50
Rows Row Source Operation
------- ---------------------------------------------------
50 VIEW (cr=12 pr=0 pw=0 time=93 us)
50 COUNT STOPKEY (cr=12 pr=0 pw=0 time=86 us)
50 VIEW (cr=12 pr=0 pw=0 time=83 us)
50 TABLE ACCESS BY INDEX ROWID T1 (cr=12 pr=0 pw=0 time=81 us)
50 INDEX RANGE SCAN DESCENDING IX_T1_2 (cr=7 pr=0 pw=0 time=49 us)
上記のテスト結果から、ROWNUM処理は不適切な状態のままで適用されていると、SQL性能に及ぼす影響が
非常に大きくなってしまうことがわかります。
従って、ROWNUMは無条件で使うのではなく、部分範囲処理するように適切に適用しましょう。
Oracle SQLチューニングブログ（第48回） 終
次回ブログテーマ
今回のブログはいかがでしたか？
次回のOracle SQLチューニングは
「 インデックス カラム順序とORDER BY節順序を合わせよう 」 です
次回もどうぞお楽しみに。
それでは See you next time！！
