ORACLE數據庫分頁查詢/翻頁
最佳實踐
一、示例數據：
Select Count(*) From dba_objects ;
-------------------------------------------------
COUNT(*)
6254
Create table ao as select * f rom dba_objects ;
Create index i_ao_created on ao(created) ;
Create index i_ao_id on ao(object_id) ;
Create table bo as select * from dba_objects where rownum<=100 ;
Analyze table ao compute statistics for table for all indexes ;
Analyze table bo compute statistics ;
二、求頁數
無論以本文中哪種SQL寫法，為了獲得查詢的總頁數，不可避免的總要進行一次COUNT(*) 的查詢過程，SQL如下：
單表：
Select count(*) into rowcount from ao where created > … and object_id between …
多表：
Select count(*) into rowcount from ao a,bo b where ao.object_id = bo.object_id and ao.object_name like …. And bo.ojbect_id > …
設單頁行數為 pagesize (缺省為 20) ，則總頁數 pagecount = ceil(rowcount / pagesize)
CEIL函數： 返回大于或等于給出數字的最小整數
SQL> select ceil(21.000001),ceil(1),ceil(10.1234) from dual;
CEIL(21.000001) CEIL(1) CEIL(10.1234)
--------------- ---------- -------------
22 1 11
提示：在具體項目中做分頁實現時，設計上如果不要求算出總頁數，則可以省去此COUNT(*) 的查詢，則整個翻頁性能將有所提升（因為少做了一個查詢）
綜上所述，對于每次的分頁查詢，需要輸入的參數有2個：
頁號： pageindex
頁行數： PAGESIZE （一般為整個系統設一個常量，如前面定義的20）
則每個頁面應該顯示的記錄為：
IF pageindex <= pagecount THEN
Firstrow = (pageindex – 1)*PAGESIZE + 1
Lastrow = pageindex * PAGESIZE
END IF;
三、分頁查詢實現SQL方案
方案一：（單表分頁查詢）
適用場景：
A、 WHERE部分條件僅僅通過索引查詢就可滿足所有查詢過濾條件；
B、滿足A條件，且有ORDER BY ，且WHERE 條件中所使用的索引，正好適合 ORDER BY 字段的要求。
SQL如下：
select /*+ ordered use_nl(v3 t) */
t.*
from (select v2.rd
from (select v1.rd, rownum rn
from (select a.rowid rd
from ao a
where a.created >= sysdate - 100
order by created) v1
where rownum <= 40) v2 -- 40 ：lastrow
where rn >= 21) v3, -- 21 ：firstrow
ao t
where v3.rd = t.rowid
執行計劃：
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 VIEW
3 2 COUNT (STOPKEY)
4 3 VIEW
5 4 INDEX (RANGE SCAN) OF 'I_AO_CREATED' (NON-UNIQUE)
6 1 TABLE ACCESS (BY USER ROWID) OF 'AO'
評價：該種場景下的分頁SQL寫法執行效率最高效，但限制較多（見適用場景）。
不適用場景：
以上述SQL為例，如果WHERE 條件部分為 a.created >= sysdate – 100 and a.object_name like ‘ABC%’ and … 即不適應于場景A條件所述，或有ORDER BY ，如：ORDER BY OBJECT_NAME ,CREATED ，即不適應于場景B條件所述，則方案一SQL不適用。
方案二：（單表/多表分頁查詢）
適用場景：
所有場景都適用，包括方案一所指場景。
SQL如下（單表）：
select v2.*
from (select v1.*, rownum rn
from (select object_name,
object_id,
owner,
data_object_id,
created
from ao
where created >= sysdate - 100
and owner = 'SYS'
order by object_name, created) v1
where rownum <= 40) v2 -- 40 ：lastrow
where rn >= 21 -- 21 ：firstrow
執行計劃：
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW
2 1 COUNT (STOPKEY)
3 2 VIEW
4 3 SORT (ORDER BY STOPKEY)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'AO'
6 5 INDEX (RANGE SCAN) OF 'I_AO_CREATED' (NON-UNIQUE)
SQL如下（多表）
select v2.*
from (select v1.*, rownum rn
from (select ao.object_name,
ao.owner,
ao.created,
bo.object_type,
bo.last_ddl_time
from ao, bo
where ao.object_id = bo.object_id
and ao.created >= sysdate - 300
and ao.owner = 'SYS'
and bo.status = 'VALID'
order by ao.object_name, bo.created) v1
where rownum <= 40) v2 -- 40：lastrow
where rn >= 21 -- 21: firstrow
執行計劃：
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW
2 1 COUNT (STOPKEY)
3 2 VIEW
4 3 SORT (ORDER BY STOPKEY)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'AO'
6 5 NESTED LOOPS
7 6 TABLE ACCESS (FULL) OF 'BO'
8 6 INDEX (RANGE SCAN) OF 'I_AO_ID'
評價：以上兩種（單表/多表）的分頁查詢寫法很類似，從性能上來說都遜于方案一的做法，但這和場景有關，是沒有辦法的事。
綜述：本文所述的兩種分頁方案，中心是闡述分頁的實現思想，即翻頁由數據庫查詢來完成，不占用額外的前后端會話內存，不在前端/中間層緩存數據，實現翻頁時方法盡量簡單、實用，需要強調的是，不論以哪種方案實現翻頁，都要求查詢的主體部分（SQL中粗體部分）的WHERE條件要求高效，這樣整個翻頁的執行速度才會快。方案一是取合理的利用了索引的特性；方案二則在主查詢不變的基礎上通過靈活運用ROWNUM偽列，實現查詢行的定位，具體應用當中能夠使用哪種方案，需具體評估場景，在之前請深刻理解本文兩種方案中示例SQL的實現。
應當特別注意一個問題，在獲得分頁的查詢SQL時，注意WHERE條件部分不應該是拼出來的，而應該是以綁定變量的方式設置條件字段值！！
SQL形如：
select v2.*
from (select v1.*, rownum rn
from (select object_name,
object_id,
owner,
data_object_id,
created
from ao
where created >= :created
and owner = :owner
order by object_name, created) v1
where rownum <= :lastrow) v2
where rn >= :firstrow