今天做了分页查询的性能测试,发现写一个好的分页查询语句,性能查询的差别是巨大!
建立测试表
create table test as select * from dba_objects
执行如下分页查询语句1:
set autotrace on
select * from (select t.*, rownum rn from (select * from TEST) t where rownum < 40) where rn >21
执行计划如下:仅仅六个逻辑读.
Execution Plan
----------------------------------------------------------
Plan hash value: 891187116
----------------------------------------------------------
Plan hash value: 891187116
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39 | 7410 | 2 (0)| 00:00:01 |
|* 1 | VIEW | | 39 | 7410 | 2 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL| TEST | 39 | 3549 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39 | 7410 | 2 (0)| 00:00:01 |
|* 1 | VIEW | | 39 | 7410 | 2 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL| TEST | 39 | 3549 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
1 - filter("RN">21)
2 - filter(ROWNUM<40)
2 - filter(ROWNUM<40)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
2158 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
18 rows processed
执行如下分页查询语句2:
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TEST) A
)
WHERE RN BETWEEN 21 AND 40
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TEST) A
)
WHERE RN BETWEEN 21 AND 40
执行计划如下:807个逻辑读.
Execution Plan
----------------------------------------------------------
Plan hash value: 607533624
----------------------------------------------------------
Plan hash value: 607533624
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 59588 | 10M| 188 (7)| 00:00:03 |
|* 1 | VIEW | | 59588 | 10M| 188 (7)| 00:00:03 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS FULL| TEST | 59588 | 5295K| 188 (7)| 00:00:03 |
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 59588 | 10M| 188 (7)| 00:00:03 |
|* 1 | VIEW | | 59588 | 10M| 188 (7)| 00:00:03 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS FULL| TEST | 59588 | 5295K| 188 (7)| 00:00:03 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
1 - filter("RN"<=40 AND "RN">=21)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
807 consistent gets
0 physical reads
0 redo size
2219 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
总结,对于一个高访问量的网站,如果采用第一种的分页查询,性能会有巨大的提升!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8410760/viewspace-731957/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8410760/viewspace-731957/