往期回顾：

        SQL优化经典等价改写1——case when 改造

1. ROWNUM基础与使用限制

ROWNUM是Oracle的一个伪列，为查询返回的行顺序分配编号：第一行为1，第二行为2，依此类推。它常用于限制查询返回的总行数，且不能以任何表名作为前缀。
核心要点：
ROWNUM与>, >=, =, BETWEEN…AND等条件组合时会出现意外结果。原因在于Oracle先从数据源获取一行，分配ROWNUM=1，再根据条件判断是否保留。如果条件是ROWNUM>1，该行会被删除，然后获取下一行，但该行又被重新分配ROWNUM=1，再次被删除，如此循环导致无数据返回。因此，ROWNUM通常只应用于<和<=条件场景。

2. 分页查询中的ROWNUM应用

在分页查询中（如查询第1-10条记录），简单的ROWNUM>=1 AND ROWNUM<=10无法正常工作。我们有两种主要改写方式：

2.1 测试环境准备

drop table test_rownum purge;
create table test_rownum as select * from dba_objects;
select count(*) from test_rownum;
   COUNT(*)
----------
    111052
alter session set statistics_level=all;
set linesize 1000
set pagesize 500

2.2 写法1：嵌套查询使用别名

将ROWNUM包装为结果集的一部分并赋予别名，然后对别名进行条件过滤：

select * from 
  (select t.*, rownum as rn from test_rownum t) a 
where a.rn>=1 and a.rn<=10;

执行计划：

SQL_ID  b3z5zu2apz8zu, child number 0
-------------------------------------
select * from (select t.*,rownum as rn from test_rownum t) a where a.rn>=1 and a.rn<=10
Plan hash value: 1157459331
--------------------------------------------------------------------------------------
| Id  |Operation           | Name       |Starts |E-Rows |A-Rows |   A-Time  |Buffers |
--------------------------------------------------------------------------------------
|*  1 | VIEW               |            |     1 |   101K|    10 |00:00:00.16|   1723 |
|   2 |  COUNT             |            |     1 |       |   111K|00:00:00.01|   1723 |
|   3 |   TABLE ACCESS FULL| TEST_ROWNUM|     1 |   101K|   111K|00:00:00.01|   1723 |
--------------------------------------------------------------------------------------          
0  sorts (disk)

问题分析：
执行计划显示，该写法扫描了全表(A-Rows=111K)，Buffers达到1723，性能较差。这是因为虽然使用了别名，但仍然需要先处理整个表再过滤。

2.3 写法2：先限制后过滤

将上限条件前置，下限条件后置的嵌套查询：

select * from 
  (select t.*, rownum as rn from test_rownum t where rownum<=10) a 
where a.rn>=1;

执行计划：

SQL_ID  5jvt2xsjrqc33, child number 0
-------------------------------------
select * from (select t.*,rownum as rn from test_rownum t where rownum<=10) a where a.rn>=1
Plan hash value: 2232708344
------------------------------------------------------------------------------------
| Id |Operation           |Name       |Starts |E-Rows |A-Rows |   A-Time  |Buffers |
------------------------------------------------------------------------------------
|*  1| VIEW               |           |     1 |    10 |    10 |00:00:00.01|      5 |
|*  2|  COUNT STOPKEY     |           |     1 |       |    10 |00:00:00.01|      5 |
|   3|   TABLE ACCESS FULL|TEST_ROWNUM|     1 |   101K|    10 |00:00:00.01|      5 |
------------------------------------------------------------------------------------

3. 执行计划对比分析

通过比较两种写法的执行计划，发现关键差异：

1）资源消耗：
写法1: Buffers = 1723
写法2: Buffers = 5
差距达345倍！

2）扫描行数：
写法1: A-Rows = 111K (全表扫描)
写法2: A-Rows = 10 (仅扫描所需行数)

3）执行方式：
写法1: Operation = “COUNT” (普通计数)
写法2: Operation = “COUNT STOPKEY” (达到条件即停止)

4. 优化原理解析

写法1的问题在于：虽然通过别名解决了ROWNUM不能用>=比较的问题，但内层查询仍会处理全表数据，外层再根据RN条件过滤。

写法2的优势在于：

内层查询使用ROWNUM<=10直接限制了处理行数
外层查询仅对这10行进行RN>=1的过滤
Oracle优化器识别到STOPKEY操作，一旦获取足够行数就停止处理

5. 结论

在Oracle分页查询中，正确使用ROWNUM可以显著提升性能。通过将上限条件(ROWNUM <= N)前置，我们可以让Oracle尽早停止扫描，避免不必要的全表处理。

执行计划清晰展示了两种写法的性能差异：写法2的"先限制后过滤"策略将资源消耗降低了345倍，这在处理大型表时尤为重要。

理解ROWNUM的工作原理及其限制，是编写高效Oracle分页查询的关键。写法2不仅能正确返回所需数据，还能确保数据库以最高效的方式执行查询。