Rownum与Order by
rownum是数据表的一个虚列，是随着select获取到的数据集合而依次生成的数据项目。借助rownum，很多复杂的SQL需求都得以解决。但是，rownum绝不是一个数据列，使用它的时候一定要注意rownum的生成原理和特性。本篇就从rownum与order by结合，谈使用rownum的一个注意点。
简单的说，rownum是在使用select查询的时候，自动生成的一串顺序号。在每返回一条数据结果的时候，rownum就自动加一。rownum可以使用在select后的列表中，也可以使用在where条件后。Oracle中没有top n命令，通常都是使用rownum来控制返回数据的条数。
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> select * from emp where rownum<3; //使用rownum来限制条数
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
rownum与>符号
我们大都知道一个原则，就是rownum在where后面的时候，要慎用>符号。当使用rownum>XXX的时候，返回结果是错误的。
SQL> select * from emp where rownum>3;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
明明数据表中有数据，但是使用rownum>3的条件时，却是一条也无法搜到。刚刚接触的时候，觉得很诡异。让我们一起看看执行计划。
SQL> explain plan for select * from emp where rownum>3;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------
Plan hash value: 2063368778
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |
| 1 | COUNT | | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM>3)
15 rows selected
注意，Oracle生成的执行计划比较简单。对EMP进行全表扫描，将每条记录进行筛选（filter操作），符合条件的记录才会最后返回给前台。筛选filter的条件就是rownum>3。
解释这个现象，还要从rownum的原理入手。rownum并不是一列事先写好的数值，而是随着select的结果集而动态推进的一个数据序列。rownum初始为1，有一条返回的时候，rownum推进数1。解释刚刚的rownum>3，开始有一条数据返回的时候，rownum为1。但是应用filter条件的时候，因为没有满足rownum>1的条件，所以该行数据被筛去，rownum没有被推进1。这时候第二条结果来，同样不会推进rownum，这样就是一个数据结果都没有。
根据这个思路，我们可以让rownum>XX起效果。
SQL> select count(*) from emp where rownum>0;
//出现结果集合
COUNT(*)
----------
14
当rownum为1初始值的时候，满足了filter的条件，所以能够顺利推进rownum取值。
SQL> explain plan for select * from emp where rownum>0;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2063368778
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |
| 1 | COUNT | | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM>0)
15 rows selected
rownum与order by
我们回到本篇的正题，关于rownum与order by。我们有时候遇到这样的需求：“列出按XXX从高到低（逆序）的前五行”。排序+控制显示行数，就是order by和rownum结合的场景。
首先，我们构建一下实验环境。
SQL> conn scott/tiger@orcl;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
SQL> create table t as select rownum as row_num, object_name from user_objects;
Table created
SQL> select * from t;
ROW_NUM OBJECT_NAME
---------- --------------------------------------------------------------------------------
1 PK_DEPT
2 DEPT
（篇幅原因，省略部分内容…）
20 SOU
21 TAR
21 rows selected
//收集统计量
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
首先，我们实验一条SQL，也是笔者当年的一种写法（惭愧ing…）。
SQL> col object_name format a20;
SQL> select * from t where rownum<5 order by row_num desc;
ROW_NUM OBJECT_NAME
---------- --------------------
4 PK_EMP
3 EMP
2 DEPT
1 PK_DEPT
结果显然是不正确的！排序是做到了，但是是在找到的几个数据中进行的排序。我们看看执行计划。
SQL> explain plan for select * from t where rownum<5 order by row_num desc;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 846588679
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 52 | 3 (0)| 00:00:01 |
| 1 | SORT ORDER BY | | 4 | 52 | 3 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL| T | 21 | 273 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<5)
15 rows selected
这里，我们看到了一个操作“COUNT STOPKEY”，应该是由于rownum而产生的。根据Oracle官方文档的解释，如下：
“Count operation where the number of rows returned is limited by
the ROWNUM expression in the WHERE clause.”
含义是，这是一个计数操作。当在where子句中使用rownum的时候，会引入这个操作。这个操作的作用就是限制返回的行数，当返回数量达到rownum的限制（filter）的时候，就结束操作，不会去管剩下的数据。
这样的解释就对了，我们的数据表T里面虽然需要进行全排序才能确定最大的四个row_num行，但是当前SQL的执行计划是“选取四行，在排序”。这样显然不是我们的需要了。
那么，如何在不改变SQL结构的情况下，获取到正确的结果呢？
那么，我们是不是要放弃这个SQL结构了呢？笔者从网络上获取一个方案，如果将order by后面字段为主键字段，这种SQL结构是可以返回正确的结果的。下面实验：
SQL> alter table t add constraint pk_t primary key (row_num);//加主键
Table altered
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);//重新收集统计量
PL/SQL procedure successfully completed
SQL> select * from t where rownum<5 order by row_num desc;
ROW_NUM OBJECT_NAME
---------- --------------------------------------------------------------------------------
21 TAR
20 SOU
19 T
18 PCK_IWB_UNI_CLOSING
//返回了正确的结果！SQL语句没有变化！
按照主键排序，就没有问题了。我们检查一下这里面的执行计划。
SQL> explain plan for select * from t where rownum<5 order by row_num desc;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 949946574
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 52 | 2 (0)| 00:00
|* 1 | COUNT STOPKEY | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 21 | 273 | 2 (0)| 00:00
| 3 | INDEX FULL SCAN DESCENDING| PK_T| 4 | | 1 (0)| 00:00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<5)
15 rows selected
这里的执行计划是这样，order by + 主键之后。CBO选择了读取数据表T的主键PK_T所对应的索引PK_T。索引的叶节点都是有序的，按照DESCENDING逆序读取索引树，取到数据表行rowid的列表。注意，这个rowid的列表是按照row_num逆序的顺序确立的列表。在第2步中，根据rowid列表访问数据表T，获取数据行（此时也是按照row_num逆序的）。最后从第1步的COUNT STOPKEY拦截住四行数据。
这种方法，借助了两个Oracle特性：其一是主键索引有序的特性，Order by的排序借助已经排好的索引完成；其二是CBO优化器，探索出这样的执行路径。
一般情况下，还是不要书写这样的SQL。对rownum，标准的做法是将其和order by分开进行处理。借助查询子句，可以获取到正确的结果。
//已经取消了主键；
SQL> desc t;
Name Type Nullable Default Comments
----------- ------------- -------- ------- --------
ROW_NUM NUMBER Y
OBJECT_NAME VARCHAR2(128) Y
//将order by单独进行处理，明确表示：先排序，后rownum；
SQL> select * from (select * from t order by row_num desc) where rownum<5;
ROW_NUM OBJECT_NAME
---------- --------------------------------------------------------------------------------
21 TAR
20 SOU
19 T
18 PCK_IWB_UNI_CLOSING
这种语法方法，可以在不受order by条件的情况下，也能实现需求。我们一起来看看执行计划。
SQL> explain plan for select * from (select * from t order by row_num desc) where rownum<5;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3299198703
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 316 | 3 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 21 | 1659 | 3 (0)| 00:00:01 |
|* 3 | SORT ORDER BY STOPKEY| | 21 | 273 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T | 21 | 273 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<5)
3 - filter(ROWNUM<5)
17 rows selected
这是一个值得我们研究的执行计划，里面有一些有意思的内容。
首先，我们看到排序order by转化为操作“SORT ORDER BY STOPKEY”。这个操作是Oracle优化SQL的一个表现。在这个步骤中，加入了filter（rownum<5）。这个操作在view操作内部。说明，虽然我们将rownum<5写在子查询的外面，但Oracle还是将这个条件渗透到了视图化操作中。
SORT ORDER BY STOPKEY操作经常能够在rownum+orderby的嵌套查询中看到。这个操作是Oracle处理rownum+orderby的一种优化算法。这个操作本质上很像算法中的快排序。首先是将数据集合分成两个部分（A和B），保证A中排序值都不小于B中排序值。在对A集合分割成两部分，依次进行。数据集B和其他那些小数据集合就不进行排序了。这样操作，只进行较少的排序次数，就可以最快的获取全局最大（或者最小）的几个数据。
当然，SORT ORDER BY STOPKEY操作在我们这个SQL中应用是没有问题的。但是并不代表没有缺陷和陷阱。rownum和order by使用最多的场合是分页，使用SORT ORDER BY STOPKEY如果不当，有可能引发其他一些功能上的缺陷。这个问题我们以后找机会谈。
其次一个关注点就是VIEW操作。显然，对嵌套的查询，Oracle使用了VIEW操作，将查询结果作为一个视图，提供给外层操作使用。
最后，就是外层依然使用了count stopkey的操作，对结果集合进行拦截。因为在内层的查询中已经进行排序+拦截操作，可以保证结果是正确的。
