Oracle数据库中Web分页方法详细介绍

rudolf@TEST902>create table t nologging
2　as select object_name product_name,mod(object_id,4)*10 category,
3object_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

　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
------------------------------ ---------- ------------
T1039　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

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)
10　 VIEW (Cost=436 Card=5263 Bytes=1094704)
21 COUNT
32　 VIEW (Cost=436 Card=5263 Bytes=1026285)
43 SORT (ORDER BY) (Cost=436 Card=5263 Bytes=1010496)
54　 TABLE ACCESS (BY INDEX ROWID) OF 'T'
(Cost=284 Card=5263 Bytes=1010496)
65 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

rudolf@TEST902>select * from t
2　 where category = &category_id
3　 order by product_name
4

rudolf@TEST902>select * from
2　( select rownum rnm, a.* from
3( select * from t where category = &category_id
4order 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

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)
10　 VIEW (Cost=284 Card=20 Bytes=4160)
21 COUNT (STOPKEY)
32　 VIEW (Cost=284 Card=5263 Bytes=1026285)
43 TABLE ACCESS (BY INDEX ROWID) OF 'T'
(Cost=284 Card=
5263 Bytes=1010496)
54　 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