分页查询必须有COUNT STOPKEY裁剪，另外要使用索引消除排序才是{BANNED}最佳高效的。注意取前N行和第M到第N行的两重嵌套和三重嵌套写法。
1.分页查询错误的写法，排序和rownum写在一起，如果排序和索引顺序不一致，则乱序
另外这个写法还有个问题，是先给rownum取个别名，在外面按照rn<=20 and rn>10，则没有COUNT STOPKEY，需要扫描全部满足
object_id>1的行，没有裁剪，效率低，逻辑读513行，扫描73086行。
select object_id,object_name,rn
from(
select object_id,object_name,rownum rn from (
select object_id,object_name from a
where object_id>1
order by object_id
)
) where rn<=20 and rn>10;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
lan hash value: 849580118
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.04 | 513 |
|* 1 | VIEW | | 1 | 82638 | 10 |00:00:00.04 | 513 |
| 2 | COUNT | | 1 | | 73086 |00:00:00.02 | 513 |
| 3 | VIEW | | 1 | 82638 | 73086 |00:00:00.02 | 513 |
|* 4 | INDEX RANGE SCAN| IDX_A | 1 | 82638 | 73086 |00:00:00.01 | 513 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("RN">10 AND "RN"<=20))
4 - access("OBJECT_ID">1 AND "OBJECT_ID" IS NOT NULL)
2.正确写法：如果是直接查询前N行，排序后外层直接rownum<=,如果查询M到N行，则排序后第二层先rownum<=,并且rownum取别名，在{BANNED}最佳外层rn>
这样内层的rownum<=则可以利用COUNT STOKEY裁剪
select object_id,object_name,rn
from
(
select object_id,object_name,rownum rn
from(
select object_id,object_name from a
where object_id >1
order by object_id
) where rownum<=20
) where rn > 10;
Plan hash value: 1967601737
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 3 |
|* 1 | VIEW | | 1 | 20 | 10 |00:00:00.01 | 3 |
|* 2 | COUNT STOPKEY | | 1 | | 20 |00:00:00.01 | 3 |
| 3 | VIEW | | 1 | 82638 | 20 |00:00:00.01 | 3 |
|* 4 | INDEX RANGE SCAN| IDX_A | 1 | 82638 | 20 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">10)
2 - filter(ROWNUM<=20)
4 - access("OBJECT_ID">1 AND "OBJECT_ID" IS NOT NULL)
3.类似的如果无条件的分页查询，也是需要按照上述正确规则编写
这个也是直接rownum取别名，然后外层按照别名过滤，没有COUNT STOKEY，扫描所有行，逻辑读1425
select object_id,object_name
from(
select object_id,object_name,rownum rn from a
) where rn<=20 and rn>10;
Plan hash value: 1160771924
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.02 | 1425 |
|* 1 | VIEW | | 1 | 82638 | 10 |00:00:00.02 | 1425 |
| 2 | COUNT | | 1 | | 73088 |00:00:00.01 | 1425 |
| 3 | TABLE ACCESS FULL| A | 1 | 82638 | 73088 |00:00:00.01 | 1425 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("RN">10 AND "RN"<=20))
这个是不澳洲写法，内层先rownum<=,能够用到COUNT STOPKEY，只需要扫描20行结束，逻辑读5，效率高。
select object_id,object_name,rn
from(
select object_id,object_name,rownum rn from a
where rownum <= 20
) where rn>10;
Plan hash value: 1899157197
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 5 |
|* 1 | VIEW | | 1 | 20 | 10 |00:00:00.01 | 5 |
|* 2 | COUNT STOPKEY | | 1 | | 20 |00:00:00.01 | 5 |
| 3 | TABLE ACCESS FULL| A | 1 | 82638 | 20 |00:00:00.01 | 5 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">10)
2 - filter(ROWNUM<=20)
