使用ORACLE數據庫時的WEB分頁方法
2024-08-29 13:41:05
供稿：網友
隨著Internet技術的發展，Web已越來越多的被應用到各行各業。傳統的基于大機或C/S結構的應用也正逐漸的為B/S（Browser/Server）結構所代替。而數據庫，作為保存著大量信息的容器，使得WEB應用能夠提供更加豐富多彩，及時、個性化的信息。 在WEB應用中，我們經常碰到需要從數據庫搜索出滿足某個特征的數據記錄，再顯示給特定用戶。經常這些滿足條件的記錄如此之多，一方面在同一個頁面顯示顯得異常臃腫而不切實際，另一方面用戶通常也不會對他們都感愛好，他們似乎更關心按一定規則排序出現在某些開始位置的若干記錄。這就要求我們對滿足條件的數據進行分頁，將用戶更關心的記錄放在首頁，同時給予是否繼續瀏覽（或跳躍式閱讀）到指定頁甚至最后一頁的自由。在這里，我們希望和大家討論一下使用Oracle數據庫時的WEB分頁方法。
我們說，一個好的分頁方法，它應當滿足以下幾個要求：
1. 數據庫處理的數據量最小；
2. 數據庫與WEB應用服務器之間的數據量傳輸最小；
假定我們有如下的業務：行業產品表，10萬記錄，字段包括產品名稱，所在行業，市場價格。要求選擇某個行業時，列出該行業下所有產品，并按產品名稱排序，超過20條的，按每頁20條分頁：
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',300,'b') supplier
4 from all_objects order by 2,1
5 /
Table created.
rudolf@TEST902>select count(*) from t;
COUNT(*)
----------
21110
用以上語句，我們快速生成了一個行業產品表，其中all_objects為oracle的一個系統表（我們經常可以使用類似的方法生成測試數據）。接下來，我們創建了索引，并為使用CBO分析了表，分析顯示該表共用去1039個數據塊：
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
為了便于討論，我們先來看一下傳統的做法:
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
這里我們使用了三個變量，其中category_id表示用戶感愛好的行業，而minrnm,maxrnm則來模擬web程序控制分頁時傳入的最小、最大行號。我們希望選出行業為20，屬于第289頁的所有產品信息。我們猜測上述語句將按以下步驟執行：
1. 取出所有滿足category=&category_id的記錄
2. 按product_name進行排序
3. 在排序完畢的結果集中取出第&minrnm到&maxrnm記錄之間的數據
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
我們可以根據執行計劃第二列的數字來閱讀計劃，即數字大的最先執行，如“5 index (range scan)”，數字相等時，按從上到下的順序執行。
上述執行計劃顯示了與我們估計相同的順序，我們看到滿足where條件的記錄一共5263條左右（第4步中的 card=5263），它們全部被取出，并參與排序（第3步），并在將結果集返回給用戶前，一直在處理所有的5263條記錄。然而事實上用戶似乎只關心本頁即20條記錄。顯然它與我們關于數據庫處理量最小的要求相距甚遠。在分析部分，284個一致讀進一步說明數據庫處理了所有滿足條件的記錄（整個表占1039個數據塊，共4個擁有相近產品數的行業，則每個行業約占259個數據塊）。
現在，我們把上述語句換成：
rudolf@TEST902>select * from t
2 where category = &category_id
3 order by product_name
4
將滿足條件的所有記錄取到客戶端（在這里為WEB應用服務器），然后利用編程語言對結果集分頁。以java為例，可以使用ResultSet對象方法absolute直接定位記錄而方便地將結果集分頁。然而很顯然，它甚至滿足關于數據庫與WEB應用服務器之間的數據量傳輸最小的要求，很多情況下將明顯影響性能，嚴重時甚至會導致WEB應用服務器一端內存溢出。言歸正傳，我們開始引入我們的方法。
方法一：同分析傳統做法類似，我們先列出我們的方法：
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
與傳統做法不同，我們把對最大行號的判定從第三層移到了第二層。改變雖然簡單，然而它表達了一個完全不同的執行意圖。內部視圖：
select rownum rnm, a.* from
( select * from t where category = &category_id
order by category,product_name
) a where rownum <= &maxrnm
是8i引入的新操作，在執行計劃中，它體現為stopkey。這種操作專門為提取TOP n的需求做了優化。它需要排序字段預先建有索引，由于索引是已排序好的結構，因此取TOP n的問題，就變為從索引中直接從頭提取n個索引要害字，然后再根據索引就可快速的找到記錄并返回給用戶。從而有效避免了檢索全部記錄的情況。
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
應將count(stopkey)操作與table access(by index rowid)結合起來看，這樣一來，table access(by index rowid)實際上只處理了&maxrnm條記錄，這里為20條。它的執行計劃可以解釋為：
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]
與傳統方法相
