【IT168 技术】1、通常的分页写法，也是第一种分页方法，类似如下方式：
select * from (
select a.*, rownum rn from
(select * from test a order by object_name) a
where rownum <=1000)
where rn > 990;
这种方式，是对表进行排序翻页，比较常见，但是，第一页与第1000页的性能差异还是挺明显的。
2、第二种的分页写法是对索引进行翻页操作，然后根据rowid 去表中取数据。 这种方式，第一页与第1000页性能相差不大。
以下语句虽然使用HINT指定使用索引， 但是仍然没有生效。
select b.* from (
select * from (
select a.*, rownum rn from
(select /*+ index(a ix_object_name) */ rowid rid from test a order by object_name) a
where rownum <=20)
where rn > 10) a, test b
where a.rid = b.rowid;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1616265646
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 2420 | | 319 (1)| 00:00:04 |
| 1 | NESTED LOOPS | | 20 | 2420 | | 319 (1)| 00:00:04 |
|* 2 | VIEW | | 20 | 500 | | 299 (1)| 00:00:04 |
|* 3 | COUNT STOPKEY | | | | | | |
| 4 | VIEW | | 23713 | 277K| | 299 (1)| 00:00:04 |
|* 5 | SORT ORDER BY STOPKEY | | 23713 | 741K| 944K| 299 (1)| 00:00:04 |
| 6 | TABLE ACCESS FULL | TEST | 23713 | 741K| | 89 (0)| 00:00:02 |
| 7 | TABLE ACCESS BY USER ROWID| TEST | 1 | 96 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RN">10)
3 - filter(ROWNUM<=20)
5 - filter(ROWNUM<=20)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
327 consistent gets
0 physical reads
0 redo size
2032 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
添加not null 约束之后，就使用索引了。
SQL> alter table test modify object_name not null;
Table altered.
Elapsed: 00:00:00.02
SQL> select b.* from (
2 select * from (
3 select a.*, rownum rn from
4 (select /*+ index(a ix_object_name) */ rowid rid from test a order by object_name) a
5 where rownum <=20)
6 where rn > 10) a, test b
7 where a.rid = b.rowid;
10 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2741793364
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 2420 | 22 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 20 | 2420 | 22 (0)| 00:00:01 |
|* 2 | VIEW | | 20 | 500 | 2 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | VIEW | | 20 | 240 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | IX_OBJECT_NAME | 20 | 640 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY USER ROWID| TEST | 1 | 96 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RN">10)
3 - filter(ROWNUM<=20)Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
2032 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
最后几页： 这种方式还不如全表扫描的方式：
select b.* from (
select * from (
select a.*, rownum rn from
(select rowid rid from test a order by object_name) a
where rownum <=20000)
where rn > 19990) a, test b
where a.rid = b.rowid;
10 rows selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 221961112
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 2363K| 179 (1)| 00:00:03 |
|* 1 | HASH JOIN | | 20000 | 2363K| 179 (1)| 00:00:03 |
|* 2 | VIEW | | 20000 | 488K| 89 (0)| 00:00:02 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | VIEW | | 20000 | 234K| 89 (0)| 00:00:02 |
| 5 | INDEX FULL SCAN| IX_OBJECT_NAME | 23713 | 741K| 89 (0)| 00:00:02 |
| 6 | TABLE ACCESS FULL | TEST | 23713 | 2223K| 90 (2)| 00:00:02 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("from$_subquery$_002"."RID"="B".ROWID)
2 - filter("RN">19990)
3 - filter(ROWNUM<=20000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
415 consistent gets
0 physical reads
0 redo size
2040 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> select * from (
2 select a.*, rownum rn from
3 (select * from test a order by object_name) a
4 where rownum <=20000)
5 where rn > 19990;
10 rows selected.
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 2340039655
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 4296K| | 613 (1)| 00:00:08 |
|* 1 | VIEW | | 20000 | 4296K| | 613 (1)| 00:00:08 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 23713 | 4793K| | 613 (1)| 00:00:08 |
|* 4 | SORT ORDER BY STOPKEY| | 23713 | 2223K| 3120K| 613 (1)| 00:00:08 |
| 5 | TABLE ACCESS FULL | TEST | 23713 | 2223K| | 90 (2)| 00:00:02 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">19990)
2 - filter(ROWNUM<=20000)
4 - filter(ROWNUM<=20000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
325 consistent gets
0 physical reads
0 redo size
2161 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
不应该啊! 全索引扫描不会比全表扫描性能更差吧。
原来，以上性能差的原因是因为使用了hash join 方式， 我们指定使用NL方式看一下：
select /*+ use_nl(a b) */ b.* from (
select * from (
select a.*, rownum rn from
(select rowid rid from test a order by object_name) a
where rownum <=20000)
where rn > 19990) a, test b
where a.rid = b.rowid;
10 rows selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 2741793364
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 2363K| 20097 (1)| 00:04:02 |
| 1 | NESTED LOOPS | | 20000 | 2363K| 20097 (1)| 00:04:02 |
|* 2 | VIEW | | 20000 | 488K| 89 (0)| 00:00:02 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | VIEW | | 20000 | 234K| 89 (0)| 00:00:02 |
| 5 | INDEX FULL SCAN | IX_OBJECT_NAME | 23713 | 741K| 89 (0)| 00:00:02 |
| 6 | TABLE ACCESS BY USER ROWID| TEST | 1 | 96 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RN">19990)
3 - filter(ROWNUM<=20000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
92 consistent gets
0 physical reads
0 redo size
2049 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
