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で最新の日付順の上位５件だけ取得する場合。
まずは、インデックスとヒント句なしで確認
- SQL実行
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 DESCとorder by num部分でのソートが2回実行されています。
これでは遅いです。
次にインデックスを作成し、ヒント句（/+ FIRST_ROWS /）を追加して確認
等価条件のstatusとROW_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);
- SQL実行
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件）分のフェッチ回数で終了していることがわかります。
最新の日付順の上位５件だけ取得する場合。
ROW_NUMBER()のソート処理を排除するためにadddate desc でインデックスを作成し、実行すると。。。
- インデックス作成
create index idx_adddate_desc on paging_table(adddate desc);
- SQL実行
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に未来の日付はないとして、全行抽出されるように書き換える。
- SQL実行
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件だけ取得したいとする。
まずは、インデックスなしで確認
- SQL実行
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);
- SQL実行
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行となるとインデックスのシングルブロックアクセスで全件読むことになるので遅くなるので注意が必要です。
