読者です 読者をやめる 読者になる 読者になる

kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL

Oracleでページング処理を高速化する方法

oracleでのページング処理を行う場合、指定した件数のみのフェッチ回数で処理終了するようにSQLを記述してあげることで高速に処理できます。
mysqlのlimit句のような動きが可能になります。
そのためには必要なことは
適切なカラムに対してインデックスを作成する。
ヒント句を使用する。

上記の目的は実行計画にWINDOW NOSORT STOPKEYを狙うことです。
このWINDOW NOSORT STOPKEYが重要で、ソート処理を排除(NO SORT)し、指定した件数でフェッチを終了(STOP KEY)するため高速で処理します。
また、ROW_NUMBER()を使用して、抽出することで表示されます。
よって、今回はROW_NUMBER()ですべて検証します。
環境はもちろんoracle 11.2.0.4 SEone。

準備

  • テーブル作成
create table paging_table (id int primary key,status int,adddate date);
  • 値を10万件挿入する
insert into paging_table 
        select level,
               mod(level,15),
               TO_DATE('1950/01/01 00:00:00','YYYY/MM/DD  HH24:MI:SS') + MOD(ABS(DBMS_RANDOM.RANDOM()),TO_DATE(TO_CHAR(CURRENT_DATE, 'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD  HH24:MI:SS') - TO_DATE('1950/01/01 00:00:00','YYYY/MM/DD  HH24:MI:SS')) 
        from dual connect by level < 100001;
  • テーブルの中身
id・・1から100000までの連番
status・・1から15までの値
adddate・・ランダムな日付
  • このようなテーブルができました。
select * from paging_table where rownum < 10;

        ID     STATUS ADDDATE
---------- ---------- -------------------
       734         14 1987/06/01 03:10:36
       735          0 1968/09/20 21:09:42
       736          1 1971/06/28 15:05:48
       737          2 1950/04/02 09:55:48
       738          3 1971/09/01 05:18:48
       739          4 1954/09/26 21:17:18
       740          5 2005/09/18 16:47:48
       741          6 1992/11/17 23:49:18
       742          7 1989/12/30 09:47:00

statusが0で最新の日付順の上位5件だけ取得する場合。

まずは、インデックスとヒント句なしで確認

select * from 
     (select row_number() over (ORDER BY adddate DESC) num,pt.* 
      from paging_table pt where status=0) 
where num between 1 and 5 ORDER BY num;
       NUM         ID     STATUS ADDDATE
---------- ---------- ---------- -------------------
         1      19665          0 2015/07/19 15:11:12
         2       3660          0 2015/07/18 05:24:24
         3      30810          0 2015/07/16 19:13:18
         4      64590          0 2015/07/12 21:50:30
         5      82095          0 2015/07/08 09:02:12

------------------------------------------------------------------------------------------
| Id  | Operation                 | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |              |  6667 |   312K|   106   (4)| 00:00:02 |
|   1 |  SORT ORDER BY            |              |  6667 |   312K|   106   (4)| 00:00:02 |
|*  2 |   VIEW                    |              |  6667 |   312K|   105   (3)| 00:00:02 |
|*  3 |    WINDOW SORT PUSHED RANK|              |  6667 |   104K|   105   (3)| 00:00:02 |
|*  4 |     TABLE ACCESS FULL     | PAGING_TABLE |  6667 |   104K|   104   (2)| 00:00:02 |
------------------------------------------------------------------------------------------

 316  consistent gets
 2  sorts (memory)

TABLE ACCESS FULLになっているため全件取得したあとにソート行っています。
読み込みブロック数も316ブロックadddate DESCorder by num部分でのソートが2回実行されています。
これでは遅いです。

次にインデックスを作成し、ヒント句(/+ FIRST_ROWS /)を追加して確認

等価条件のstatusROW_NUMBER()のソート処理を排除するためにadddate descにインデックスを作成します。
そしてヒント句FIRST_ROWSを記述して実行します。

FIRST_ROWSについて・・・

最初のn行を最も効率的に戻す計画を選択し、個々のSQL文を最適化して応答時間を速くするようOracleに指示します。
オプティマイザは問合せの最適化アプローチを使用して、次の文を最善の応答時間に最適化します。
Oracle® Database SQL言語リファレンス11gリリース2 (11.2)

  • インデックス作成
create index idx_status_adddate_desc on paging_table(status,adddate desc); 
select /*+ FIRST_ROWS */  * from 
     (select row_number() over (ORDER BY adddate DESC) num,pt.* 
     from paging_table pt where status=0) 
where num between 1 and 5 ORDER BY num;

       NUM         ID     STATUS ADDDATE
---------- ---------- ---------- -------------------
         1      19665          0 2015/07/19 15:11:12
         2       3660          0 2015/07/18 05:24:24
         3      30810          0 2015/07/16 19:13:18
         4      64590          0 2015/07/12 21:50:30
         5      82095          0 2015/07/08 09:02:12

----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                         |  6667 |   312K|  6674   (1)| 00:01:21 |
|   1 |  SORT ORDER BY                 |                         |  6667 |   312K|  6674   (1)| 00:01:21 |
|*  2 |   VIEW                         |                         |  6667 |   312K|  6673   (1)| 00:01:21 |
|*  3 |    WINDOW NOSORT STOPKEY       |                         |  6667 |   104K|  6673   (1)| 00:01:21 |
|   4 |     TABLE ACCESS BY INDEX ROWID| PAGING_TABLE            |  6667 |   104K|  6673   (1)| 00:01:21 |
|*  5 |      INDEX RANGE SCAN          | IDX_STATUS_ADDDATE_DESC |  6667 |       |    24   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
 8  consistent gets
 1  sorts (memory)

こうすることで先ほど作成したインデックスを使用し、WINDOW NOSORT STOPKEYが効き、読み込みブロック数がたったの8ブロック、ソートが1回
指定した回数(5件)分のフェッチ回数で終了していることがわかります。

最新の日付順の上位5件だけ取得する場合。

ROW_NUMBER()のソート処理を排除するためにadddate desc でインデックスを作成し、実行すると。。。

  • インデックス作成
create index idx_adddate_desc on paging_table(adddate desc);
select /*+ FIRST_ROWS */  * from 
      (select row_number() over (ORDER BY adddate DESC) num,pt.* 
       from paging_table pt) 
where num between 1 and 5 ORDER BY num;

       NUM         ID     STATUS ADDDATE
---------- ---------- ---------- -------------------
         1      47649          9 2015/07/22 17:43:54
         2      25753         13 2015/07/22 03:01:12
         3      51043         13 2015/07/21 18:04:06
         4      89444         14 2015/07/21 14:30:48
         5      28308          3 2015/07/21 10:11:30
--------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |              |   100K|  4687K|       |  1842   (1)| 00:00:23 |
|   1 |  SORT ORDER BY            |              |   100K|  4687K|  5896K|  1842   (1)| 00:00:23 |
|*  2 |   VIEW                    |              |   100K|  4687K|       |   642   (2)| 00:00:08 |
|*  3 |    WINDOW SORT PUSHED RANK|              |   100K|  1562K|  2760K|   642   (2)| 00:00:08 |
|   4 |     TABLE ACCESS FULL     | PAGING_TABLE |   100K|  1562K|       |   104   (2)| 00:00:02 |
--------------------------------------------------------------------------------------------------
316  consistent gets
2  sorts (memory)

あれ??効いていない。。
where句内に何か条件を指定してあげないとWINDOW NOSORT STOPKEYが効かないみたい。
ということでadddateに未来の日付はないとして、全行抽出されるように書き換える。

select /*+ FIRST_ROWS */  * from 
       (select row_number() over (ORDER BY adddate DESC) num,pt.* 
       from paging_table pt where adddate < sysdate + 1) 
where num between 1 and 5 ORDER BY num;

       NUM         ID     STATUS ADDDATE
---------- ---------- ---------- -------------------
         1      47649          9 2015/07/22 17:43:54
         2      25753         13 2015/07/22 03:01:12
         3      51043         13 2015/07/21 18:04:06
         4      89444         14 2015/07/21 14:30:48
         5      28308          3 2015/07/21 10:11:30
         
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |   100K|  4687K|       |   101K  (1)| 00:20:15 |
|   1 |  SORT ORDER BY                 |                  |   100K|  4687K|  5896K|   101K  (1)| 00:20:15 |
|*  2 |   VIEW                         |                  |   100K|  4687K|       |   100K  (1)| 00:20:01 |
|*  3 |    WINDOW NOSORT STOPKEY       |                  |   100K|  1562K|       |   100K  (1)| 00:20:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| PAGING_TABLE     |   100K|  1562K|       |   100K  (1)| 00:20:01 |
|*  5 |      INDEX RANGE SCAN          | IDX_ADDDATE_DESC |   100K|       |       |   285   (2)| 00:00:04 |
-----------------------------------------------------------------------------------------------------------
 8  consistent gets
 1  sorts (memory)

無事WINDOW NOSORT STOPKEYが効いて、5行にフェッチで終了してるみたい。なかなか難しいですね。。

続いて、join場合はどうすればよいか。

joinの場合は非常に難しく、正しくないかもしれません。試行錯誤して見つけた法則なもので。
nested loop joinでなくてはならない。(厳密にはMERGE JOIN CARTESIANも効くかも。。。)
適切なインデックスを作成する。
結合順序を意識する。(row_numberで順序付けするテーブルを駆動表する)

準備

  • テーブル作成
create table paging_table2 (id int primary key,status int,text varchar2(10));
  • 値を20000件挿入する
insert into paging_table2 select level,mod(level,10000),DBMS_RANDOM.STRING('l',10) from dual connect by level < 20001;
  • テーブルの中身
id・・1から20000までの連番
status・・1から10000までの値
text・・ランダムな文字列
  • このような感じのテーブル。
select * from paging_table2 where rownum < 10;
        ID     STATUS TEXT
---------- ---------- ----------
       312        312 jvuczbjktt
       313        313 szdjiehcia
       314        314 uduuhvuhju
       315        315 aohirxjskd
       316        316 nynuxajaxj
       317        317 ibsgvrqpkj
       318        318 vjzbckxmbs
       319        319 ivawvrddvf
       320        320 fdjbavfyyc

statusが0でpaging_tableの最新の日付順でtext行を上位5件だけ取得したいとする。

まずは、インデックスなしで確認

select * from 
    (select row_number() over (ORDER BY adddate DESC) num,adddate,status,text 
    from paging_table pt join paging_table2 pt2 
    using(status) where status=0) 
where num between 1 and 5 ORDER BY num;

       NUM ADDDATE                 STATUS TEXT
---------- ------------------- ---------- ----------
         1 2015/07/19 15:11:12          0 yfoeucnzbz
         2 2015/07/19 15:11:12          0 mknbmyswfs
         3 2015/07/18 05:24:24          0 yfoeucnzbz
         4 2015/07/18 05:24:24          0 mknbmyswfs
         5 2015/07/16 19:13:18          0 yfoeucnzbz


-----------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                         | 15615 |   640K|    49   (9)| 00:00:01 |
|   1 |  SORT ORDER BY            |                         | 15615 |   640K|    49   (9)| 00:00:01 |
|*  2 |   VIEW                    |                         | 15615 |   640K|    47   (5)| 00:00:01 |
|*  3 |    WINDOW SORT PUSHED RANK|                         | 15615 |   472K|    47   (5)| 00:00:01 |
|*  4 |     HASH JOIN             |                         | 15615 |   472K|    46   (3)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL    | PAGING_TABLE2           |     2 |    40 |    22   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN     | IDX_STATUS_ADDDATE_DESC |  6738 | 74118 |    23   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
 98  consistent gets
 2  sorts (memory)

先ほど作成したインデックスがうまく効いているが、HASH JOINとなってるので全行フェッチされている。読み取りブロック数も98ブロック

次に適切なインデックスを追加(内部表をカバーリングインデックスで作成)し、hash joinではなくnested loop joinとなるようにと結合順序をpaging_tableを駆動表となるようにヒント句を追加する。
* インデックス作成

create index idx_status_text on paging_table2(status,text);
select * from 
    (select  /*+ USE_NL(pt pt2) LEADING(pt pt2) */
     row_number() over (ORDER BY adddate DESC) num,adddate,status,text 
    from paging_table pt join paging_table2 pt2 
    using(status) where status=0) 
where num between 1 and 5 ORDER BY num;

       NUM ADDDATE                 STATUS TEXT
---------- ------------------- ---------- ----------
         1 2015/07/19 15:11:12          0 mknbmyswfs
         2 2015/07/19 15:11:12          0 yfoeucnzbz
         3 2015/07/18 05:24:24          0 mknbmyswfs
         4 2015/07/18 05:24:24          0 yfoeucnzbz
         5 2015/07/16 19:13:18          0 mknbmyswfs

---------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                         | 15615 |   640K|  6767   (1)| 00:01:22 |
|   1 |  SORT ORDER BY          |                         | 15615 |   640K|  6767   (1)| 00:01:22 |
|*  2 |   VIEW                  |                         | 15615 |   640K|  6765   (1)| 00:01:22 |
|*  3 |    WINDOW NOSORT STOPKEY|                         | 15615 |   472K|  6765   (1)| 00:01:22 |
|   4 |     NESTED LOOPS        |                         | 15615 |   472K|  6765   (1)| 00:01:22 |
|*  5 |      INDEX RANGE SCAN   | IDX_STATUS_ADDDATE_DESC |  6738 | 74118 |    23   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN   | IDX_STATUS_TEXT         |     2 |    40 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
7  consistent gets
1  sorts (memory)

読み込みブロック数が7ブロックとなりソートも減りました。
少し気になるのがadddateが同一で合った場合のtextの順番は逆になってます。

まとめ

WINDOW NOSORT STOPKEYを狙えば読み取りブロック数も減らせ高速になることは間違いないです。 しかし、それは直近N件で条件にマッチする行数が多ければ有効ですが、マッチする件数が100000行分1行となるとインデックスのシングルブロックアクセスで全件読むことになるので遅くなるので注意が必要です。