隨著Internet技術(shù)的發(fā)展，Web已越來(lái)越多的被應(yīng)用到各行各業(yè)。傳統(tǒng)的基于大機(jī)或C/S結(jié)構(gòu)的應(yīng)用也正逐漸的為B/S（Browser/Server）結(jié)構(gòu)所代替。而數(shù)據(jù)庫(kù)，作為保存著大量信息的容器，使得WEB應(yīng)用能夠提供更加豐富多彩，及時(shí)、個(gè)性化的信息。在WEB應(yīng)用中，我們經(jīng)常遇到需要從數(shù)據(jù)庫(kù)搜索出滿足某個(gè)特征的數(shù)據(jù)記錄，再顯示給特定用戶。常常這些滿足條件的記錄如此之多，一方面在同一個(gè)頁(yè)面顯示顯得異常臃腫而不切實(shí)際，另一方面用戶通常也不會(huì)對(duì)他們都感興趣，他們似乎更關(guān)心按一定規(guī)則排序出現(xiàn)在某些開始位置的若干記錄。這就要求我們對(duì)滿足條件的數(shù)據(jù)進(jìn)行分頁(yè)，將用戶更關(guān)心的記錄放在首頁(yè)，同時(shí)給予是否繼續(xù)瀏覽（或跳躍式閱讀）到指定頁(yè)甚至最后一頁(yè)的自由。在這里，我們希望和大家討論一下使用ORACLE數(shù)據(jù)庫(kù)時(shí)的WEB分頁(yè)方法。
我們說，一個(gè)好的分頁(yè)方法，它應(yīng)當(dāng)滿足以下幾個(gè)要求：
1. 數(shù)據(jù)庫(kù)處理的數(shù)據(jù)量最?。?
2. 數(shù)據(jù)庫(kù)與WEB應(yīng)用服務(wù)器之間的數(shù)據(jù)量傳輸最??；
假定我們有如下的業(yè)務(wù)：行業(yè)產(chǎn)品表，10萬(wàn)記錄，字段包括產(chǎn)品名稱，所在行業(yè)，市場(chǎng)價(jià)格。要求選擇某個(gè)行業(yè)時(shí)，列出該行業(yè)下所有產(chǎn)品，并按產(chǎn)品名稱排序，超過20條的，按每頁(yè)20條分頁(yè)：
rudolf@TEST902>create table t nologging
2 as select object_name product_name,mod(object_id,4)*10 category,
3 object_id price,rpad(‘a(chǎn)‘,300,‘b‘) supplier
4 from all_objects order by 2,1
5 /
Table created.
rudolf@TEST902>select count(*) from t;
COUNT(*)
----------
21110
用以上語(yǔ)句，我們快速生成了一個(gè)行業(yè)產(chǎn)品表，其中all_objects為oracle的一個(gè)系統(tǒng)表（我們常?？梢允褂妙愃频姆椒ㄉ蓽y(cè)試數(shù)據(jù)）。接下來(lái)，我們創(chuàng)建了索引，并為使用CBO分析了表，分析顯示該表共用去1039個(gè)數(shù)據(jù)塊：
rudolf@TEST902>create index t_category_pname_ind on t (category,product_name)
2 nologging
3 tablespace indx
4 /
Index created.
rudolf@TEST902>analyze table t compute statistics
2 for table
3 for all indexes
4 for all indexed columns
5 /
Table analyzed.
rudolf@TEST902>select table_name,blocks,empty_blocks from user_tables where table_name = ‘T‘;
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
T 1039 113
為了便于討論，我們先來(lái)看一下傳統(tǒng)的做法:
rudolf@TEST902>select * from
2 ( select rownum rnm, a.* from
3 ( select * from t where category = &category_id
4 order by product_name
5 ) a
6 ) where rnm between &minrnm and &maxrnm
7
這里我們使用了三個(gè)變量，其中category_id表示用戶感興趣的行業(yè)，而minrnm,maxrnm則來(lái)模擬web程序控制分頁(yè)時(shí)傳入的最小、最大行號(hào)。我們希望選出行業(yè)為20，屬于第289頁(yè)的所有產(chǎn)品信息。我們猜測(cè)上述語(yǔ)句將按以下步驟執(zhí)行：
1. 取出所有滿足category=&category_id的記錄
2. 按product_name進(jìn)行排序
3. 在排序完畢的結(jié)果集中取出第&minrnm到&maxrnm記錄之間的數(shù)據(jù)
rudolf@TEST902>set autot trace
rudolf@TEST902>/
Enter value for category_id: 20
Enter value for minrnm: 4981
Enter value for maxrnm: 5000
20 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=436 Card=5263 Bytes=1094704)
1 0 VIEW (Cost=436 Card=5263 Bytes=1094704)
2 1 COUNT
3 2 VIEW (Cost=436 Card=5263 Bytes=1026285)
4 3 SORT (ORDER BY) (Cost=436 Card=5263 Bytes=1010496)
5 4 TABLE ACCESS (BY INDEX ROWID) OF ‘T‘ (Cost=284 Card=5263 Bytes=1010496
)
6 5 INDEX (RANGE SCAN) OF ‘T_CATEGORY_PNAME_IND‘ (NON-UNIQUE) (Cost=31 C
ard=5263)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
284 consistent gets
0 physical reads
0 redo size
1829 bytes sent via SQL*Net to client
514 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
20 rows processed
我們可以根據(jù)執(zhí)行計(jì)劃第二列的數(shù)字來(lái)閱讀計(jì)劃，即數(shù)字大的最先執(zhí)行，如“5 index (range scan)”，數(shù)字相等時(shí)，按從上到下的順序執(zhí)行。上述執(zhí)行計(jì)劃顯示了與我們估計(jì)相同的順序，我們看到滿足where條件的記錄一共5263條左右（第4步中的 card=5263），它們?nèi)勘蝗〕?，并參與排序（第3步），并在將結(jié)果集返回給用戶前，一直在處理所有的5263條記錄。然而事實(shí)上用戶似乎只關(guān)心本頁(yè)即20條記錄。顯然它與我們關(guān)于數(shù)據(jù)庫(kù)處理量最小的要求相距甚遠(yuǎn)。在分析部分，284個(gè)一致讀進(jìn)一步說明數(shù)據(jù)庫(kù)處理了所有滿足條件的記錄（整個(gè)表占1039個(gè)數(shù)據(jù)塊，共4個(gè)擁有相近產(chǎn)品數(shù)的行業(yè)，則每個(gè)行業(yè)約占259個(gè)數(shù)據(jù)塊）。
現(xiàn)在，我們把上述語(yǔ)句換成：
rudolf@TEST902>select * from t
2 where category = &category_id
3 order by product_name
4
將滿足條件的所有記錄取到客戶端（在這里為WEB應(yīng)用服務(wù)器），然后利用編程語(yǔ)言對(duì)結(jié)果集分頁(yè)。以JAVA為例，可以使用ResultSet對(duì)象方法absolute直接定位記錄而方便地將結(jié)果集分頁(yè)。然而很顯然，它甚至滿足關(guān)于數(shù)據(jù)庫(kù)與WEB應(yīng)用服務(wù)器之間的數(shù)據(jù)量傳輸最小的要求，很多情況下將明顯影響性能，嚴(yán)重時(shí)甚至?xí)?dǎo)致WEB應(yīng)用服務(wù)器一端內(nèi)存溢出。言歸正傳，我們開始引入我們的方法。
方法一：同分析傳統(tǒng)做法類似，我們先列出我們的方法：
rudolf@TEST902>select * from
2 ( select rownum rnm, a.* from
3 ( select * from t where category = &category_id
4 order by category,product_name
5 ) a where rownum <= &maxrnm
6 ) where rnm >= &minrnm
7
與傳統(tǒng)做法不同，我們把對(duì)最大行號(hào)的判斷從第三層移到了第二層。改變雖然簡(jiǎn)單，然而它表達(dá)了一個(gè)完全不同的執(zhí)行意圖。內(nèi)部視圖：
select rownum rnm, a.* from
( select * from t where category = &category_id
order by category,product_name
) a where rownum <= &maxrnm
是8i引入的新操作，在執(zhí)行計(jì)劃中，它體現(xiàn)為stopkey。這種操作專門為提取TOP n的需求做了優(yōu)化。它需要排序字段預(yù)先建有索引，由于索引是已排序好的結(jié)構(gòu)，因此取TOP n的問題，就變?yōu)閺乃饕兄苯訌念^提取n個(gè)索引關(guān)鍵字，然后再根據(jù)索引就可快速的找到記錄并返回給用戶。從而有效避免了檢索全部記錄的情況。
rudolf@TEST902>set autot trace
rudolf@TEST902>set verify off
Enter value for category_id: 20
Enter value for maxrnm: 20
Enter value for minrnm: 1
20 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=284 Card=20 Byte
s=4160)
1 0 VIEW (Cost=284 Card=20 Bytes=4160)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=284 Card=5263 Bytes=1026285)
4 3 TABLE ACCESS (BY INDEX ROWID) OF ‘T‘ (Cost=284 Card=
5263 Bytes=1010496)
5 4 INDEX (RANGE SCAN) OF ‘T_CATEGORY_PNAME_IND‘ (NON-
UNIQUE) (Cost=31 Card=5263)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1848 bytes sent via SQL*Net to client
514 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
應(yīng)將count(stopkey)操作與table access(by index rowid)結(jié)合起來(lái)看，這樣一來(lái)，table access(by index rowid)實(shí)際上只處理了&maxrnm條記錄，這里為20條。它的執(zhí)行計(jì)劃可以解釋為：
rnm := 1;
for rec in (select * from t where category = &category_id order by category, product_name)
loop
rnm := rnm + 1;
if rnm > [$maxrnm then exit loop] end if;
fetch rec;
end loop;
filter rec where rownum < [$minrnm]
與傳統(tǒng)方法相比，它大大減小了數(shù)據(jù)庫(kù)處理的壓力：284個(gè)一致讀減小為7個(gè)，性能因此得到了改善。然而也許你注意到了，當(dāng)用戶不停的向后翻頁(yè)，使得&maxrnm逐漸接近滿足條件的記錄數(shù)時(shí)，它的性能
也漸漸降低到與傳統(tǒng)方法相近的水平：
rudolf@TEST902>set autot trace statistics
rudolf@TEST902>select * from
2 ( select rownum rnm, a.* from
3 ( select * from t where category = &category_id
4 order by category,product_name
5 ) a where rownum <= &maxrnm
6 ) where rnm >= &minrnm
7 /
Enter value for category_id: 20
Enter value for maxrnm: 5000
Enter value for minrnm: 4981
20 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
275 consistent gets
0 physical reads
0 redo size
1829 bytes sent via SQL*Net to client
514 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
rudolf@TEST902>
我們看到，當(dāng)用戶瀏覽到第249頁(yè)時(shí)，這種方法共使用了275個(gè)一致讀，與傳統(tǒng)方法的284個(gè)一致讀已很接近了。幸運(yùn)的是，在很多應(yīng)用中，98%的用戶將只關(guān)心前5頁(yè)的數(shù)據(jù)，使得這些應(yīng)用仍能得益于這個(gè)方法。當(dāng)我們把order by子句改為order by ... desc，同時(shí)創(chuàng)建逆索引，我們甚至可以把某些用戶關(guān)心最后5頁(yè)數(shù)據(jù)的需求改變?yōu)殛P(guān)心前5頁(yè)。盡管如此，還是有某些應(yīng)用，用戶瀏覽頁(yè)面更可能是隨機(jī)的，這時(shí)我們就可以用到第二種方法：
方法二：
rudolf@TEST902>select * from t
2 where rowid in
3 ( select rid from
4 ( select rownum rno,rowid rid from
5 ( select rowid from t
6 where category = &category_id
7 order by category,product_name
8 ) where rownum <= &maxrnm
9 ) where rno >= &minrnm
10 )
11
在這一方法中，我們考慮到索引與表相比，身材上大大小于后者（我們可以把它看作一個(gè)小表），因此我們?cè)噲D先在索引中搜索出某頁(yè)記錄的物理位置，然后根據(jù)這些物理位置（rowid）在表中直接取出相應(yīng)的記錄，我們認(rèn)為它將消除前一種方法中index range scan所有滿足條件記錄時(shí)帶來(lái)的高成本（到某一刻CBO甚至認(rèn)為它高于FULL TABLE SCAN而選擇FULL TABLE SCAN）。
Enter value for category_id: 20
Enter value for maxrnm: 5000
Enter value for minrnm: 4981
20 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=5054 Card=5000 Bytes=1095000)
1 0 NESTED LOOPS (Cost=5054 Card=5000 Bytes=1095000)
2 1 VIEW (Cost=31 Card=5000 Bytes=100000)
3 2 SORT (UNIQUE)
4 3 COUNT (STOPKEY)
5 4 VIEW (Cost=31 Card=5263 Bytes=36841)
6 5 INDEX (RANGE SCAN) OF ‘T_CATEGORY_PNAME_IND‘ (NON-UNIQUE) (Cost=31 C
ard=5263 Bytes=178942)
7 1 TABLE ACCESS (BY USER ROWID) OF ‘T‘ (Cost=1 Card=1 Bytes=199)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
50 consistent gets
0 physical reads
0 redo size
1551 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
20 rows processed
我們可以看到語(yǔ)句的執(zhí)行邏輯：
rnm := 1;
for rec in (select * from t_category_pname_ind
where category = &category_id order by category, product_name)
loop
rnm := rnm + 1;
if rnm > [$maxrnm then exit loop] end if;
fetch rowid;
end loop;
filter rowid array where rownum < [$minrnm]
select * from t where rowid in ( rowid array );
基本上，無(wú)論用戶瀏覽哪頁(yè)，數(shù)據(jù)庫(kù)的數(shù)據(jù)處理量都較為相近，約為index fast full scan的成本加上20次access by rowid的成本。與前一種方法相比，當(dāng)用戶只瀏覽前幾頁(yè)時(shí)，可能它的成本相對(duì)稍大，然而隨著用戶逐頁(yè)往后瀏覽，它的成本優(yōu)勢(shì)也迅速的顯現(xiàn)出來(lái)。同樣瀏覽第4981-5000條記錄，我們看到方法一產(chǎn)生了275個(gè)一致讀，而本方法僅僅產(chǎn)生了50個(gè)。對(duì)于我們“數(shù)據(jù)庫(kù)處理量最小”的要求而言，可說是大大邁進(jìn)了一步。
綜上所述，由于用戶瀏覽特點(diǎn)、習(xí)慣不同，我們可以采用不同的分頁(yè)方法，以便更有效的利用資源。
我接觸過的最厲害的是Ebay的系統(tǒng)，他們的瀏覽量太大，以至于使用數(shù)據(jù)庫(kù)來(lái)做這種排序的功能，系統(tǒng)無(wú)法負(fù)擔(dān)。
結(jié)果他們采用的是直接在WebServer上面進(jìn)行排序。
還有一個(gè)思路，就是如果訪問量實(shí)在很大，可以根據(jù)類別，把不同的列別干脆放到不同的表的里面，這樣，每個(gè)表的物理大小大大減小，每次的讀取，都會(huì)大大減少，從而降低數(shù)據(jù)庫(kù)服務(wù)器的壓力，提高相應(yīng)時(shí)間。
如果頁(yè)面的訪問量非常大，但是更新非常小的話，還可以采用另外一個(gè)技術(shù)，一個(gè)表對(duì)應(yīng)就是多個(gè)表，同樣的內(nèi)容，冗余到不同的表的里面，每個(gè)表都采用IOT技術(shù)（Index Organized Table)，原來(lái)的主鍵 ＋ 需要排序的字段，這樣，每次按照不同的排序方法，都可以非常迅速的得到需要的答案。但是這個(gè)需要應(yīng)用程序?qū)懙纳晕?fù)雜一點(diǎn)、
對(duì)于數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng).可以使用分析函數(shù)來(lái)進(jìn)行一些簡(jiǎn)化處理,在以后將給大家介紹
本站僅提供存儲(chǔ)服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)
點(diǎn)擊舉報(bào)。
