1、问题简述
系统后台数据库采用Oracle 11gR2版本。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE11.2.0.3.0Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
数据表inputfile_log记录数据文件日志信息。在界面上提供数据查询、操作和排序。界面提供所有字段的查询排序功能。数据表主键为inputfile_log_seq，一个字段为ISO_CODE。
在测试阶段，测试人员不时报出某些特定字段在排序过程中有一些问题，排序之后，跳转到下一页数据取值不变。但是，在架构层面，没有明显的故障和Bug存在，前端故障也大都是偶发性的。
一个偶然的机会，测试和开发人员定位到了问题的所在：对其他字段进行排序处理之后，分页功能一般不会有故障。只有在ISO_CODE进行排序，之后进行跳转分页的时候，才会有问题，而且问题也是偶发性，不具有必然性。
经过定位，发现页面前端输出的SQL语句有一些问题。页面前端采用Hibernate实体映射类。分页采用数据库“真分页”技术——数据库只把要显示的数据传递到前端。Hibernate负责SQL语句的生成执行。
定位的SQL语句为：
select *
from (select a1.inputfile_log_seq, a1.ISO_CODE,rownum rwn
from (select * from inputfile_log t order by t.ISO_CODE) a1
where rownum <= 50)
where rwn > 45;
Oracle中分页的语句方案很多，这种利用rownum取段的方法也是普遍接受的方法。问题在哪儿呢？下面我们执行语句看结果。
SQL> select *
2from (select a1.inputfile_log_seq, a1.ISO_CODE,rownum rwn
3from (select * from inputfile_log t order by t.ISO_CODE) a1
4where rownum <= 50)
5where rwn > 45;
INPUTFILE_LOG_SEQ ISO_CODERWN
----------------- -------- ----------
1901 AU46
1903 AU47
1906 AU48
1142 AU49
1157 AU50
SQL> select *
2from (select a1.inputfile_log_seq, a1.ISO_CODE, rownum rwn
3from (select * from inputfile_log t order by t.ISO_CODE) a1
4where rownum <= 55)
5where rwn > 50;
INPUTFILE_LOG_SEQ ISO_CODERWN
----------------- -------- ----------
1901 AU51
1903 AU52
1906 AU53
1142 AU54
1157 AU55
看出问题了，在SQL语句中，我们希望看到连续10条记录。但是虽然替换了数据段范围，但是结果集却是相同的。
反映到页面上，就是按照ISO_CODE进行排序之后，分页功能失效，点击跳到下一页，但是显示的数据却没有变化。
2、分析和实验
看似很诡异的问题，难道是Hibernate语句方案有问题？替换为其他的排序字段，问题似乎不存在。
SQL> select *
2from (select a1.inputfile_log_seq, a1.ISO_CODE,rownum rwn
3from (select * from inputfile_log t order by t.inputfile_log_seq) a1
4where rownum <= 55)
5where rwn > 50;
INPUTFILE_LOG_SEQ ISO_CODERWN
----------------- -------- ----------
997 AU51
998 AU52
999 AU53
1000 AU54
1001 AU55
SQL>
SQL> select *
2from (select a1.inputfile_log_seq, a1.ISO_CODE,rownum rwn
3from (select * from inputfile_log t order by t.inputfile_log_seq) a1
4where rownum <= 50)
5where rwn > 45;
INPUTFILE_LOG_SEQ ISO_CODERWN
----------------- -------- ----------
992 AU46
993 AU47
994 AU48
995 AU49
996 AU50
排序结果正常。看来问题还是出在ISO_CODE上。
条条大路通罗马，换一种方法，试试结果如何呢？Oracle中还有很多其他的分页方案。
SQL> select *
2from (select a1.inputfile_log_seq, a1.ISO_CODE,rownum rwn
3from (select * from inputfile_log t order by t.ISO_CODE) a1)
4where rwn > 45
5and rwn <= 50;
INPUTFILE_LOG_SEQ ISO_CODERWN
----------------- -------- ----------
1316 AU46
1317 AU47
1318 AU48
1319 AU49
1323 AU50
SQL>
SQL> select *
2from (select a1.inputfile_log_seq, a1.ISO_CODE, rownum rwn
3from (select * from inputfile_log t order by t.ISO_CODE) a1)
4where rwn > 50
5and rwn <= 55;
INPUTFILE_LOG_SEQ ISO_CODERWN
----------------- -------- ----------
1324 AU51
1865 AU52
1624 AU53
1163 AU54
1173 AU55
不采用问题SQL的两次截取方法，而是将所有的rownum都取出来实体化，再利用范围进行截取。这样做的结果也是正确的。
问题出在哪里呢？SQL本质上是一种描述性语句，只要我们正确描述，绝大多数情况是可以将正确的结果返回的。但是这个案例下，描述本身没有什么问题，而且错误出现是偶发性的，令人疑惑。