ORACLE數(shù)據(jù)庫分頁查詢/翻頁
最佳實(shí)踐
一、示例數(shù)據(jù)：
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 ;
二、求頁數(shù)
無論以本文中哪種SQL寫法，為了獲得查詢的總頁數(shù)，不可避免的總要進(jìn)行一次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 > …
設(shè)單頁行數(shù)為 pagesize (缺省為 20) ，則總頁數(shù) pagecount = ceil(rowcount / pagesize)
CEIL函數(shù)： 返回大于或等于給出數(shù)字的最小整數(shù)
SQL> select ceil(21.000001),ceil(1),ceil(10.1234) from dual;
CEIL(21.000001) CEIL(1) CEIL(10.1234)
--------------- ---------- -------------
22 1 11
提示：在具體項(xiàng)目中做分頁實(shí)現(xiàn)時(shí)，設(shè)計(jì)上如果不要求算出總頁數(shù)，則可以省去此COUNT(*) 的查詢，則整個(gè)翻頁性能將有所提升（因?yàn)樯僮隽艘粋€(gè)查詢）
綜上所述，對(duì)于每次的分頁查詢，需要輸入的參數(shù)有2個(gè)：
頁號(hào)： pageindex
頁行數(shù)： PAGESIZE （一般為整個(gè)系統(tǒng)設(shè)一個(gè)常量，如前面定義的20）
則每個(gè)頁面應(yīng)該顯示的記錄為：
IF pageindex <= pagecount THEN
Firstrow = (pageindex – 1)*PAGESIZE + 1
Lastrow = pageindex * PAGESIZE
END IF;
三、分頁查詢實(shí)現(xiàn)SQL方案
方案一：（單表分頁查詢）
適用場(chǎng)景：
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
執(zhí)行計(jì)劃：
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'
評(píng)價(jià)：該種場(chǎng)景下的分頁SQL寫法執(zhí)行效率最高效，但限制較多（見適用場(chǎng)景）。
不適用場(chǎng)景：
以上述SQL為例，如果WHERE 條件部分為 a.created >= sysdate – 100 and a.object_name like ‘ABC%’ and … 即不適應(yīng)于場(chǎng)景A條件所述，或有ORDER BY ，如：ORDER BY OBJECT_NAME ,CREATED ，即不適應(yīng)于場(chǎng)景B條件所述，則方案一SQL不適用。
方案二：（單表/多表分頁查詢）
適用場(chǎng)景：
所有場(chǎng)景都適用，包括方案一所指場(chǎng)景。
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
執(zhí)行計(jì)劃：
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
執(zhí)行計(jì)劃：
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'
評(píng)價(jià)：以上兩種（單表/多表）的分頁查詢寫法很類似，從性能上來說都遜于方案一的做法，但這和場(chǎng)景有關(guān)，是沒有辦法的事。
綜述：本文所述的兩種分頁方案，中心是闡述分頁的實(shí)現(xiàn)思想，即翻頁由數(shù)據(jù)庫查詢來完成，不占用額外的前后端會(huì)話內(nèi)存，不在前端/中間層緩存數(shù)據(jù)，實(shí)現(xiàn)翻頁時(shí)方法盡量簡(jiǎn)單、實(shí)用，需要強(qiáng)調(diào)的是，不論以哪種方案實(shí)現(xiàn)翻頁，都要求查詢的主體部分（SQL中粗體部分）的WHERE條件要求高效，這樣整個(gè)翻頁的執(zhí)行速度才會(huì)快。方案一是取合理的利用了索引的特性；方案二則在主查詢不變的基礎(chǔ)上通過靈活運(yùn)用ROWNUM偽列，實(shí)現(xiàn)查詢行的定位，具體應(yīng)用當(dāng)中能夠使用哪種方案，需具體評(píng)估場(chǎng)景，在之前請(qǐng)深刻理解本文兩種方案中示例SQL的實(shí)現(xiàn)。
應(yīng)當(dāng)特別注意一個(gè)問題，在獲得分頁的查詢SQL時(shí)，注意WHERE條件部分不應(yīng)該是拼出來的，而應(yīng)該是以綁定變量的方式設(shè)置條件字段值?。?/span>
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