Oracle中ORDER BY非唯一值时OFFSET/FETCH返回重复行的原因

这是Stack Overflow某问题的后续讨论，原问题仅展示现象未解释底层机制。

环境搭建 #

先创建测试表并插入数据：

CREATE TABLE t (x INT, name VARCHAR(100));

INSERT INTO t
SELECT level,
       CASE WHEN MOD(level,2)=0 THEN 'CDR' ELSE 'SDS' END
  FROM dual
CONNECT BY level <= 10000;

COMMIT;

上述语句生成10000行数据：

• x为唯一值，范围1到10000
• name仅有两个取值：5000行CDR（对应x为偶数）、5000行SDS（对应x为奇数）

问题现象 #

执行以下两个分页查询：

SELECT *
FROM t
ORDER BY name 
    OFFSET 1 ROWS FETCH NEXT 10 ROWS ONLY;

SELECT *
FROM t
ORDER BY name 
    OFFSET 11 ROWS FETCH NEXT 10 ROWS ONLY;

实际结果：两个查询返回完全相同的10行数据，示例输出如下：

X     NAME
-----------
1706  CDR
1704  CDR
1702  CDR
1700  CDR
1698  CDR
1696  CDR
1694  CDR
1692  CDR
1690  CDR
1688  CDR

按逻辑预期，OFFSET 1应返回第2-11行，OFFSET 11应返回第12-21行，二者结果应完全不同，但实际却出现重复。

原因分析 #

问题的核心在于排序键不唯一：
当使用ORDER BY name时，name只有两个不同值，数据库对name相同的行（比如所有CDR行）没有明确的排序规则。此时数据库会采用不确定的排序逻辑（比如数据的物理存储顺序、查询优化器临时选择的排序方式），这种逻辑在每次查询时可能发生变化。

当两次查询时，name相同的行组内部顺序不一致，导致OFFSET跳过的行并非预期的连续行，最终两次查询取到了同一批物理行。

解决方案 #

要避免这种分页重复问题，必须保证排序的确定性，即在ORDER BY中添加唯一键作为补充排序条件，比如：

SELECT *
FROM t
ORDER BY name, x 
    OFFSET 1 ROWS FETCH NEXT 10 ROWS ONLY;

SELECT *
FROM t
ORDER BY name, x 
    OFFSET 11 ROWS FETCH NEXT 10 ROWS ONLY;

通过name, x组合排序，由于x是唯一值，整个结果集的排序顺序完全确定，分页查询的结果就会符合预期。

内容的提问来源于stack exchange，提问作者Mohamed Shereef