'order by' 不确定时的 SQL 分页

Question

我多年来一直使用相同的 sql 分页代码块..我现在才注意到这个奇怪的地方..我认为这非常有趣，我们应该讨论它。所以这是标准分页样板:

  SELECT a.*
  FROM (SELECT b.*,
               rownum b_rownum
          FROM (SELECT c.*
                  FROM some_table c
                 ORDER BY some_column) b
         WHERE rownum <= <<upper limit>>) a
 WHERE b_rownum >= <<lower limit>>`

如果 some_column 是连续的，那么效果会非常好。

但我公开了这一点并允许用户对任何列进行排序，如果他们碰巧选择了充满相同值的 some_column - 分页“中断”。

也就是说，查询将逐页返回完全相同的行数据。当我想到这一点时，为什么不呢？它可能只是选择最快的行或通过过滤器的任何行。

例如，这些sql返回完全相同的数据

select *
from (select a.*, ROWNUM rnum
      from ( select * from xsd order by PREFIX asc ) a
      where ROWNUM <= 30
     )
where rnum  >= 20;


select *
from (select a.*, ROWNUM rnum
      from ( select * from xsd order by PREFIX asc ) a
      where ROWNUM <= 40
     )
where rnum  >= 30;

大多数情况下，我只是觉得这很简洁，而且我在其他帖子中没有看到太多关于这种副作用的信息。

而且，我想知道我能做些什么......以及其他策略是否会增加处理时间。

亚历克斯戈登的解决方案

只需添加 rowid 作为默认的最终排序依据。这些 SQL 现在不同了。我没有注意到响应时间有任何变化

select *
from (select a.*, ROWNUM rnum
      from ( select * from xsd order by PREFIX asc, rowid ) a
      where ROWNUM <= 30
     )
where rnum  >= 20;


select *
from (select a.*, ROWNUM rnum
      from ( select * from xsd order by PREFIX asc, rowid ) a
      where ROWNUM <= 40
     )
where rnum  >= 30;

stackoverflow 的又一次胜利，谢谢各位，祝大家编码愉快

Answer 1

SQL 表表示无序集。因此，SQL 排序不稳定。也就是说，具有相同键值的行可以采用任何顺序——并且在多次运行中顺序可以改变。行没有“基础”顺序。

解决方案是在order by末尾添加一个唯一的id。这始终是最后一个键:

select *
from (select a.*, ROWNUM as rnum
      from ( select * from xsd order by PREFIX asc, id ) a
      where ROWNUM <= 40
     )
where rnum  >= 30;

这是否影响性能取决于是否可以使用索引进行排序。如果不使用索引，那么影响应该很小。但是，如果添加额外的键会阻止使用索引，那么影响会更大。