Oracle SQLチューニング（第48回）「画面PAGINATION処理する時これは守ろう」（2／7回）

2025-06-09 06:01Oracle技術情報

Oracle SQLチューニング

　今回の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],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を作成すると下記の通りになります。

　ここで重要なのが、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！！

Oracle SQLチューニング（第48回）「画面PAGINATION処理する時これは守ろう」（2／7回）

13.1　ROWNUM処理を良くしよう

前の記事

次の記事

他社に頼らず自社でデータベースを監視・運用をしませんか？
MaxGaugeがサポートします

人気記事ランキング

タグ一覧

Oracle SQLチューニング（第48回）「 画面PAGINATION処理する時これは守ろう 」（2／7回）

13.1 ROWNUM処理を良くしよう

前の記事

次の記事

他社に頼らず自社でデータベースを監視・運用をしませんか？MaxGaugeがサポートします

人気記事ランキング

タグ一覧

Oracle SQLチューニング（第48回）「画面PAGINATION処理する時これは守ろう」（2／7回）

13.1　ROWNUM処理を良くしよう

他社に頼らず自社でデータベースを監視・運用をしませんか？
MaxGaugeがサポートします