一个分页排序SQL查询结果集不确定的案例

前几天一位运价的兄弟提出一个关于分页排序SQL的问题，比较有意思，这里分享一下。

前些日子碰巧看了杨长老这篇文章：《让SQL成为一种生活方式：认识分页查询》，以下为原文摘要：

Oracle的分页查询语句有两种基本格式。第一种格式如下：
select * from
(
select a.*, rownum rn
from (select * from table_name) a
where rownum <= 30
)
where rn>=21;
上面给出的这个分页查询语句，在大多数情况拥有较高的效率。其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM<= 40和RN >= 21控制分页查询的每页的范围。

第二种方式是去掉查询第二层的WHERE ROWNUM <= 30语句，在查询的最外层控制分页的最小值和最大值。语法如下： “`
select * from
(
select a.*, rownum rn
from (select * from table_name) a
)
where rn between 21 and 30;

对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。

对于第一个查询语句，第二层的查询条件WHERE ROWNUM <=
30就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。而第二个查询语句，由于查询条件BETWEEN 21 AND 30是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层(即使推到最内层也没有意义，因为最内层查询不知道RN代表什么)。因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。

同事提出的这条SQL，正是使用了上面提到的第一种写法。以下是叙述的信息，其中SQL做了脱敏，不影响原义。

第一条SQL是不带分页的查询语句，结果集中有一条CLS_CODE是B。
(此处原文有一些限定条件，确保第一条SQL的结果集肯定包含第二条和第三条的结果集，即CLS_CODE=B应该只有一条记录在第二条或第三条SQL的结果集中。)

SELECT T.C_CODE, (CASE T.FLG WHEN 'C' THEN 0 WHEN 'A' THEN 1 END) AS FLG FROM TABLE T LEFT JOIN RULE R ON TRIM(R.CODE) = 'PUB' AND R.NO = T.FILE_NO AND R._REC_NO = T.NO WHERE T.CODE = 'CA' AND T.D_DATE >= to_Date('2016-08-10 23:59:59','yyyy-mm-dd hh24:mi:ss') AND T.FIRST_DATE >= to_Date('2016-08-10 00:00:00','yyyy-mm-dd hh24:mi:ss') AND T.FIRST_DATE <= to_Date('2016-08-10 23:59:59','yyyy-mm-dd hh24:mi:ss') AND T.LAST_DATE >= to_Date('2016-08-10 23:59:59','yyyy-mm-dd hh24:mi:ss') AND T.EFF_DATE <= T.D_DATE AND T.FIRST_DATE <= T.LAST_DATE;

第二条SQL是带分页的查询语句(5000条一页的第一页)，查询结果不包含CLS_CODE是B的数据。

select * from ( select tmp_page.*, rownum row_id from (SELECT T.C_CODE, (CASE T.FLG WHEN 'C' THEN 0 WHEN 'A' THEN 1 END) AS FLG FROM TABLE T LEFT JOIN RULE R ON TRIM(R.CODE) = 'PUB' AND R.NO = T.FILE_NO AND R._REC_NO = T.NO WHERE T.CODE = 'CA' AND T.D_DATE >= to_Date('2016-08-10 23:59:59','yyyy-mm-dd hh24:mi:ss') AND T.FIRST_DATE >= to_Date('2016-08-10 00:00:00','yyyy-mm-dd hh24:mi:ss') AND T.FIRST_DATE <= to_Date('2016-08-10 23:59:59','yyyy-mm-dd hh24:mi:ss') AND T.LAST_DATE >= to_Date('2016-08-10 23:59:59','yyyy-mm-dd hh24:mi:ss') AND T.EFF_DATE <= T.D_DATE AND T.FIRST_DATE <= T.LAST_DATE ORDER BY T.CODE, T.O_STN, T.D_STN, T.FIRST_DATE, T.EFF_DATE
    ) tmp_page where rownum <= 5000) x WHERE x.row_id > 0;

第三条SQL是带分页的查询语句(5000条一页的第二页)，查询结果不包含CLS_CODE是B的数据。

select * from ( select tmp_page.*, rownum row_id from (SELECT T.C_CODE, (CASE T.FLG WHEN 'C' THEN 0 WHEN 'A' THEN 1 END) AS FLG FROM TABLE T LEFT JOIN RULE R ON TRIM(R.CODE) = 'PUB' AND R.NO = T.FILE_NO AND R._REC_NO = T.NO WHERE T.CODE = 'CA' AND T.D_DATE >= to_Date('2016-08-10 23:59:59','yyyy-mm-dd hh24:mi:ss') AND T.FIRST_DATE >= to_Date('2016-08-10 00:00:00','yyyy-mm-dd hh24:mi:ss') AND T.FIRST_DATE <= to_Date('2016-08-10 23:59:59','yyyy-mm-dd hh24:mi:ss') AND T.LAST_DATE >= to_Date('2016-08-10 23:59:59','yyyy-mm-dd hh24:mi:ss') AND T.EFF_DATE <= T.D_DATE AND T.FIRST_DATE <= T.LAST_DATE ORDER BY T.CODE, T.O_STN, T.D_STN, T.FIRST_DATE, T.EFF_DATE
    ) tmp_page where rownum <= 10000) x WHERE x.row_id > 5000;

第二次执行查询第二条和第三条SQL，两个结果又都包含CLS_CODE是B的数据，但实际应该只出现在一个查询结果中。

如果第二条和第三条SQL的order by中加过一次C_CODE之后，再怎么查都有B，即使将排序中加上的C_CODE去掉，SQL恢复到初始状态，也还是都有B，而且第一第二页都有B。

还有一些辅助的信息：
1.SQL执行过程中，没有数据变更。
2.TABLE表是普通堆表，其中
主键：NO, FILE_NO, CODE
唯一约束：RULE_ID
外键：FILE_NO, FRO，关联第三张表
索引：O_STN, D_STN, EFF_DATE, D_DATE

首先，这条分页SQL采用的是上面杨长老介绍的第一种写法，语法上正确。
其次，第一次执行第一条和第二条SQL，结果集没有C_CODE=B的记录，但实际应该至少有一个结果集中包含这条记录。
第三，第二次执行第一条和第二条SQL，两个结果集又都包含C_CODE=B的记录，但实际只应该有一个结果集包含这条记录。
最后，增加order by的C_CODE字段，无论是否再删除，第二条和第三条SQL结果集是都包含C_CODE=B的记录。
总结起来，就是C_CODE=B的记录在第一页和第二页出现的几率是随机的，并无明显的规律。

语法没错，但结果集的语义好像出现了问题，原因是什么？

原因就出在了ROWNUM的使用上。

TOM曾经写了一篇文章《On ROWNUM and Limiting Results》，非常清晰地介绍了ROWNUM的使用，值得我们反复阅读和学习。

ROWNUM是一个查询中可以使用的伪列，之所以叫伪列，是因为表中记录根本没有这个列信息。ROWNUM的取值从1，2，3一直到N，N是查询结果集的总数。ROWNUM并不会永久地赋予某一行，所以想要直接查询第五行数据是不可能的，因为表中根本没有这个数据。

ROWNUM是在他传向查询的谓词阶段之后被赋予结果集的某行记录上，而且这之后才会继续执行排序或聚集等操作，例如如下SQL

select ..., ROWNUM from t where <where clause> group by <columns> having <having clause> order by <columns>;

他的执行顺序是，
1.FROM/WHERE子句。
2.ROWNUM会被赋予FROM/WHERE子句输出结果的每一行，并且可以开始递增。
3.SELECT子句。
4.GROUP BY子句。
5.HAVING子句。
6.ORDER BY子句。

为了便于说明，我们将上面的SQL简化为，

select * from (
select tmp_page.*, rownum row_id from (
select t.c_code, ... from table t where ... order by t.code, t.o_stn, t.d_stn, t.first_date, t.eff_date) k where rownum <= 5000) x 
where x.rownum > 0;

按照上面的执行顺序，
1.执行from table t where …
2.执行where rownum <= 5000
3.执行select t.c_code, …
4.执行order by t.code, t.o_stn, t.d_stn, t.first_date, t.eff_date

前三步会得到一个5000行的结果集，第四步会对这个结果集按照t.code, t.o_stn, t.d_stn, t.first_date, t.eff_date这些字段依次做排序了，我们再结合这张表的属性，order by的这些字段没有一个能保证值唯一，换句话说，这几个字段值相同的记录可能是有重复的(实际确实是)，在这种情况下，查询结果集的顺序是不确定的，无法保证顺序。

我们借用Tom的一个实验来直观看下这种说法，首先创建测试表，id列是重复性较高的整型值，data列是1-100的随机数，

我们先查询其中的1001-1010行，

再查询1001-1011行，

order by的列id重复值较多，从两次查询结果集中可以看出，对应的所有记录ID值都是重复的0，1001-1010和1001-1011记录的行数正确，但对应的DATA值是不同的，1001-1010的第1001条记录是27，1001-1011的第1001条记录是47。这就可以证明order by字段有重复值的情况下，查询结果集的顺序是不确定的，进而我们可以推测，order by字段值的重复度和结果集不确定性的程度有关，如果order by字段值没有重复的，则结果集就是确定的。这就可以说明为什么最开始的分页SQL两次查询的结果集中，C_CODE=B的记录出现是不确定的。

再看Tom所说，

One important thing about using this pagination query is that the ORDER BY statement should order by something unique. If what you are ordering by is not unique, you should add something to the end of the ORDER BY to make it so. The rows will have a definite, deterministic order and the pagination query will deterministically return the rows as expected.

解决方案就是向order by中增加一些确保唯一的字段。这里我们选择增加rowid，

可以看出，两次查询的结果集中，相同行数的data字段值是相同的，即已经是确定的结果集了。

针对上面的问题SQL，实际采用的是唯一约束RULE_ID字段，新的SQL如下，

select * from ( select tmp_page.*, rownum row_id from (SELECT T.C_CODE, (CASE T.FLG WHEN 'C' THEN 0 WHEN 'A' THEN 1 END) AS FLG FROM TABLE T LEFT JOIN RULE R ON TRIM(R.CODE) = 'PUB' AND R.NO = T.FILE_NO AND R._REC_NO = T.NO WHERE T.CODE = 'CA' AND T.D_DATE >= to_Date('2016-08-10 23:59:59','yyyy-mm-dd hh24:mi:ss') AND T.FIRST_DATE >= to_Date('2016-08-10 00:00:00','yyyy-mm-dd hh24:mi:ss') AND T.FIRST_DATE <= to_Date('2016-08-10 23:59:59','yyyy-mm-dd hh24:mi:ss') AND T.LAST_DATE >= to_Date('2016-08-10 23:59:59','yyyy-mm-dd hh24:mi:ss') AND T.EFF_DATE <= T.D_DATE AND T.FIRST_DATE <= T.LAST_DATE ORDER BY T.CODE, T.O_STN, T.D_STN, T.FIRST_DATE, T.EFF_DATE, T.RULE_ID
    ) tmp_page where rownum <= 5000) x WHERE x.row_id > 0;

无论是查询1-5000，还是5001-10000，结果都是正确的，不再是不确定的结果集了。

总结：
使用ROWNUM做分页时，order by需要有可以唯一确定记录的字段，否则查询结果集就是不确定的，使用唯一索引字段、唯一约束字段或rowid均可。当然这没有探究SQL执行效率问题，如果涉及这部分内容，还需要具体根据实际情况来考虑字段的选择。