关于正确理解rownum使用场景的讨论

背景说明：

在某项目中收到客户提出的疑问，说同样的查询sql在两个数据库中得到的结果不一致（表中的数据是完全一致的），后来查看sql发现与rownum做分页有关。该问题对于经常写业务逻辑sql的开发人员而言可能很熟悉，但是对于刚接触数据库技术人员或客户而言可能就有点不理解了。现将该问题记录如下：

抛出问题：

如下sql的执行过程应该是怎样的？
select … from table where … order by …
对于这样一个基本格式的查询sql，我们知道执行顺序应该如下：

扫描表table
进行where条件的过滤
进行order by排序
进行选择表达式计算（即关系投影）
返回结果集
我们知道rownum是表中的伪列，一般是用在控制结果集的规模，查询的谓词解析之后才会分配rownum值的。那么当过滤条件中有rownum时执行顺序有什么变化吗？下面通过一个例子看看：

CREATE TABLE TEST(
"ID" INTEGER,
"NAME" VARCHAR(20));
insert into test select level,'abc' from dual connect by level <100000;
commit;
select  id from test where rownum<4 order by id DESC；
复制

通过结果可以看出，先做了rownum<4然后做的id倒序排序，是符合刚才所说的执行顺序的。下面我们对id列设置主键：

alter table test add primary key(id);
复制

再来看看这个sql的执行结果：

select id from test where rownum<4 order by id DESC

复制

可以看到结果与刚才不同，看起来似乎是先做了排序后做rownum<4。这是怎么回事呢？order by字段是否为主键竟然会影响结果。

执行计划对比：

当id不是主键时该sql的执行计划：

可以看到，当id不是主键或id上没有索引时，TEST做的全表扫描，然后做的RNSK（即ROWNUM<3的过滤），最后做的排序这是符合我们刚才说的执行顺序的。
当id为主键该sql的执行计划：

可以看到，当id为主键时，由于创建主键会自动创建主键索引，所以TEST表的扫描方式变成了二级索引定位，并且扫描类型为DESC即倒序扫描。通过执行计划可以看到，依然先做表的扫描，后做RNSK，但是我们知道索引的结构本身就是有序了，所以执行计划中并没有显式出现排序操作符，因为SSEK扫描表的时候数据已经有序了。所以最终的结果是所有数据倒序排列后再做ROWNUM<4，这其实并没有违背文章开头提到的执行顺序原则。

Oracle的表现是怎样的？

当Id非主键时：

从结果看当id不是主键时查询结果与达梦并不一致，为什么呢？我们知道oracle创建表时默认是堆表，oracle存储位置的顺序不一定是完全按照插入的顺序来的，而达梦默认是索引组织表，且当没有指定聚集索引时默认就是按照rowid进行顺序存储的。我们通过查询oracle中test表最小的rowid看看是否是这样：

通过结果可以看到oracle就是按照rowid的物理存储顺序返回结果的。Oracle中的rowid是实际物理存储位置的偏移（与插入顺序并不一致），而达梦的rowid是逻辑上的（与插入顺序保持一致），这就造成了返回结果不一致。当然这并不是我们所要讨论的重点，可以看到oracle的执行计划是与达梦保持一致的，也是test全表扫描，然后做rowid限制，最后做排序返回。

当设置id为主键后：

ID为主键时返回结果与计划和达梦是保持一致的。

回归主题：

我们回到最初讨论的问题，当一个查询sql中的rownum与order by共存（非嵌套子查询）时一定要小心，我们应该清楚的明白这个sql的目的是什么，因为order by的排序字段是否有主键（或索引）将会带来不同的结果。所以当我们需要通过sql来实现分页功能时应该怎么避免这个问题呢？答案是嵌套查询。
使用如下的查询样式来实现分页，不要将rownum与order by放在同一级查询中，而是加一层嵌套，将rownum放在外层。
select … from (select … from table order by …)where rownum<xx
这样，无论order by的字段上是否有主键或索引都不会影响最终结果，执行顺序都是先做排序后做分页。
我们要记住一个原则，Rownum值的分配永远总是在排序或聚合操作之前，查询谓词解析之后进行的。这样既可以很好的理解rownum的使用场景了。