1.以前听网上说rowid分页比rownum快,今天特意试验了,发现rowid并不比rownum快,
实验环境oracle 11g 11.2.0.1.0
SQL> SELECT *
2 FROM SCOTT.EMP3 WHERE ROWID IN (SELECT RID
4 FROM (SELECT ROWNUM RN, RID
5 FROM (SELECT ROWID RID, SAL FROM SCOTT.EMP ORDER BY SAL DESC
6 )
7 WHERE ROWNUM < 28
8 )
9 WHERE RN > 5
10 )
11 ORDER BY SAL DESC;
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 3686812265
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 50 | 7 (43)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 50 | 7 (43)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 50 | 6 (34)| 00:00:01 |
| 3 | VIEW | VW_NSO_1 | 14 | 168 | 4 (25)| 00:00:01 |
| 4 | HASH UNIQUE | | 1 | 350 | | |
|* 5 | VIEW | | 14 | 350 | 4 (25)| 00:00:01 |
|* 6 | COUNT STOPKEY | | | | | |
| 7 | VIEW | | 14 | 168 | 4 (25)| 00:00:01 |
|* 8 | SORT ORDER BY STOPKEY| | 14 | 224 | 4 (25)| 00:00:01 |
| 9 | TABLE ACCESS FULL | EMP | 14 | 224 | 3 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY USER ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("RN">5)
6 - filter(ROWNUM<28)
8 - filter(ROWNUM<28)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1085 bytes sent via SQL*Net to client
476 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
9 rows processed
SQL> SELECT *
2 FROM (SELECT T1.*, ROWNUM RN FROM (SELECT * FROM SCOTT.EMP ORDER BY SAL DESC) T1 WHERE ROWNUM < 28)
3 WHERE RN > 5;
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 2603316617
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 14 | 1400 | 4 (25)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 14 | 1218 | 4 (25)| 00:00:01 |
|* 4 | SORT ORDER BY STOPKEY| | 14 | 532 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">5)
2 - filter(ROWNUM<28)
4 - filter(ROWNUM<28)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1165 bytes sent via SQL*Net to client
476 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed