本帖最后由 Kevin__Zhang 于 2012-4-19 12:34 编辑
一直以来,看到的说法大都是rowid要强于rownum分页:
在索引设计的没问题的前提下,两种方式在前几页性能上接近,越向后翻rowid的效果越明显。
注意红字部分,红字部分一定成立吗?实验说明一切。
我们先来看看11g的情况,建立实验环境:
11gR2 >create table test(id number,status VARCHAR2(7),type VARCHAR2(19),created date);
Table created.
11gR2 >insert into test select OBJECT_ID,STATUS,OBJECT_TYPE,CREATED from dba_objects;
12926 rows created.
11gR2 >alter table test modify created not null;
Table altered.
11gR2 >create index test_ind1 on test(CREATED);
Index created.
11gR2 >ANALYZE TABLE TEST compute statistics;
Table analyzed.
测试11g的rownum分页:
11gR2 >select *
from (
select rownum rn,t.*
from
(select id,status,type,created from test order by created) t
where rownum<1000)
where rn >900;
99 rows selected.
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 50949 | 9 (0)| 00:00:01 |
|* 1 | VIEW | | 999 | 50949 | 9 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 999 | 37962 | 9 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEST | 12926 | 277K| 9 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | TEST_IND1 | 999 | | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
22 consistent gets 0 physical reads
0 redo size
3703 bytes sent via SQL*Net to client
590 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99 rows processed
下面测试rowid分页
11gR2 >select /*+