Rownum和Order By的执行顺序造成的影响

6月的某天下午，某用户反馈，医生站首页中病案附加项目的顺序乱了，影响医生的正常工作。

从跟踪出来的SQL看到，执行得到的数据没有按预期的方式排序，但是，相同的SQL在测试库运行却能得到正常排序的结果。

 

院方近期没有做什么调整，怀疑跟一个月前我们转移历史数据之前的一系列性能优化调整有关。

经过一番分析，最终，通过重新收集该SQL涉及到的表的统计信息后，问题得到解决。

感觉有点儿不可思议吧？

统计信息收集不正确，还会影响产品功能的正常性？

 

有些事情的真相并不是我们看到那样，就像鸡血在磨心上点了几下之后，小孩的肚子就不痛了，如果你相信这样的巫术，可能会影响到你对很多事情的判断，有时，我们看到的并不一定是真相，倒不是因为对未知的知识缺乏敬畏，而是基本的逻辑推理问题，以及探寻真相的钻研精神。

 

统计信息收集确实是解决很多性能问题的一副灵药，但对这个问题来说，收集统计信息只是其中的一种临时解决办法，根本的原因还是SQL书写方面存在问题。

下面我们来一步一步解开事情的真相。

SQL语句如下：

select rownum as 序号,编码,名称,内容 from 病案项目 order by 编码

 

多么简单的SQL，不像那些需要翻几页才能看得完整的变态SQL，这种简单的SQL除了理解起来更节约时间之外，还减少了很多可能导致验证结果偏差的干扰。

按开发人员的预期，先排序再对结果集进行编号，Rownum作为Oracle下特有的“伪列”，作用就是根据数据结果行数产生一个从1开始递增的行号。

 

有一定基础的同学可能一下就看出的不对之处：

Order by和Rownum在同一层次时，排序是最后执行的，先取行号自然就无法得到想要的按排序结果递增的行号，所以，如下所示，序号列就是“乱序”的。

 

那么，问题来了，既然这个SQL有问题，为什么在测试库运行又是正常的呢？

近期产品中的这条SQL也没有做过修改，为什么以前是正常的，现在却突然出现问题了呢？

没有骗你哟，用户发来了测试库上执行后结果正确的截图。

 

刚开始看到这个现象，也是很纳闷，会不会是数据库的什么参数影响了排序，像经常遇到的"_gby_hash_aggregation_enabled"这个参数对Group By排序的影响（后续可能会写一个这方面的案例），问题是这个SQL里没有Group by子句。

从数据库的基础理论方面想了想，影响排序的还有哪些因素呢？

如果是有索引，那么索引本身就是排了序的，读取数据时就不需要排序了，再用Rownum取值，是不是就可以取到预期的结果了呢？

也就是说，变相实现了先排序，后取序号的作用。

 

为了证实这一点，在公司的测试库上做了一个验证：

1.     相同的SQL，执行结果跟用户测试库的结果是一样的，序号正常排序。

2.     禁用主键“病案项目_PK”(以“编码”字段为索引)后，执行结果跟用户正式库的结果一样，序号排序就乱了，再现了问题。

alter table 病案项目 disable constraint 病案项目_PK;

恢复主键后，序号就正常排序了。

alter table 病案项目 enable constraint 病案项目_PK;

 

是不是用户生产库的主键被禁用了，索引丢了？

转移历史数据期间，的确会禁用一些约束，但是这张表并不是转出相关的表呀！而且转完数据后，我们恢复约束后也做过检查。

会不会是用户后期运行过程中，某种原因导致该索引无效了呢？

马上查询用户的生产库，主键是有效的，索引也是有效的。

 

眼看找到一条路，没想到走到底发现是个死胡同，不要懈气，既然问题再现了，原理也清楚了，顺着这条路，仔细找找，一定有出路。

 

在测试环境，对比分析一下，禁用主键（删除“编码”字段的索引）前后的执行计划。

对比发现了差异：

有索引时，执行计划包含” INDEX FULL SCAN”，没有排序操作。

没有索引时，执行计划包含” TABLE ACCESS FULL”，有排序操作“SORT ORDER BY”。

用户生产库的执行计划是什么呢？

 

一查询，结果跟我在这边测试环境下的执行计划一样，走了全表扫描。

为什么没有走索引全扫呢？

会不会是统计信息收集有问题，导致成本评估时，认为全表扫描的成本更低，所以选择了它呢。

 

在用户生产库收集了一下统计信息，结果就正常了，执行计划变成了” INDEX FULL SCAN”。

表的统计信息丢失的原因，不得而知。

其实解决问题的办法还有其他的，例如：通过Sql Profile加提示字指定索引。

 

既然是统计信息没有收集的问题，那是不是可以再现一下问题现象呢？

完全可以。

我们删除统计信息后来看看，是不是就再现用户生产库的场景了呢？

exec dbms_stats.delete_table_stats(ownname => 'ZLHIS',tabname => '病案项目');

再PLSQL中查看刚才那条SQL的执行计划，确实从索引全扫描变成了全表扫描。

执行SQL，查询一下数据，咦，怎么数据不是乱序的？

执行计划不是变了吗？

什么情况？

 

又到强调掌握数据库基础理论的重要性的时候了，如果只学习那些用得到的知识，书到用时方恨少，只有打开坚实的基础，才能在实战中临场应对。

 

清空一下共享池再看看。

alter system flush SHARED_POOL;

再次执行数据查询，哈哈，这次，“编码”字段终于乱序了，一阵窃喜。

数据乱了还要高兴，什么原因？因为它如你所愿，你看到了想要的结果。

 

为什么之前从PLSQL看到执行计划是全表扫描，但查询数据却跟索引全扫描的结果一样呢？

别忘了，PLSQL只是一个工具，它的F5查看执行计划的功能，不知道骗了多少人，坑了多少人。

其实你明白它查看执行计划的原理，就知道它不是真的想骗你，如果你有兴趣，可以通过10046去探个究竟，因为篇幅有限，这里就不再详述。

 

总结：

至此，这个问题搞清楚了，我们再来回看一下SQL，重新梳理一下：

select rownum as 序号,编码,名称,内容 from 病案项目 order by 编码

当Order by和Rownum在同一层次时，排序是最后执行的，先取行号再排序，这样就无法根据排序结果得到正确行号，如果能够避免排序，就能得到期望的结果。

当Order by中的字段是非空索引时（主键索引属于非空索引），如果统计信息收集正确，执行计划评估成本后，就会选择“索引全扫描”，由于索引本身是排了序的，就能避免排序。

当表的统计信息丢失，优化器在评估成本时，由于缺乏统计信息作为成本计算的依据，它就会选择“全表扫描”作为执行计划，然后再排序。

 

为了避免统计信息收集导致的这种问题，在各种用户环境下得到稳定的结果，这个SQL可以改为：

Select Rownum As 序号, 编码, 名称, 内容 From (Select 编码, 名称, 内容 From 病案项目 Order By 编码)

在子查询中先排序，外层查询中再取Rownum，类似的，当我们在写Rownum<5这类SQL时，也需要注意排序的问题，应该先在子查询中写排序，外面再限制返回行数。

 

思考：

为了加深对这个知识的理解，你可以试一下这两个SQL：

Select 姓名 From 人员表 Where Rownum < 2 Order By 建档时间 Desc;

Select 姓名 From 人员表 Where Rownum < 2 Order By ID Desc;

ID是主键，顺序递增，建档时间最大的记录，其ID最大。

为什么两个SQL得到的数据不样呢？对比一下执行计划看看。

 

知识点的扩展应用：

利用这个案例中的知识点：排序字段如果与非空索引的字段顺序相同，则可以利用索引本身已排序的特性来避免排序，这在一些高并发的高频SQL中，对系统的整体性能提升将会起到非常重要的作用，因为排序操作对cpu消耗比较大，特别是那些大的、高频的排序。

特别提醒，两个重要条件：字段顺序相同，非空索引。


------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
公众号：医信系统性能优化
主要写一些日常工作中性能优化方面的案例，包括SQL优化，数据结构设计优化，Oracle系统性能优化。
面向编写SQL及相关脚本的开发人员和技术支持人员，分享一些性能优化的经验。
对性能优化技术学习感兴趣的同学，欢迎订阅，共同学习，相互交流。