sql - 使用复合键的键集分页

Question

我正在使用 oracle 12c 数据库，我有一个具有以下结构的表:

Id       NUMBER
SeqNo    NUMBER
Val      NUMBER
Valid    VARCHAR2

使用字段 Id 和 SeqNo 创建复合主键。

我想使用 Valid = 'Y' 获取数据并应用页面大小为 3 的 ketset 分页。假设我有以下数据:

Id    SeqNo    Val    Valid
1     1        10     Y
1     2        20     N
1     3        30     Y
1     4        40     Y
1     5        50     Y
2     1        100    Y
2     2        200    Y

预期结果:

----------------------------
Page 1
----------------------------
Id    SeqNo    Val    Valid
1     1        10     Y
1     3        30     Y
1     4        40     Y
----------------------------
Page 2
----------------------------
Id    SeqNo    Val    Valid
1     5        50     Y
2     1        100    Y
2     2        200    Y

偏移分页可以这样实现:

SELECT * FROM table ORDER BY Id, SeqNo OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;

然而，在实际的数据库中，它有超过 500 万条记录，使用 OFFSET 会大大减慢查询速度。因此，我正在寻找一种 ketset 分页方法(使用一些唯一字段而不是 OFFSET 跳过记录)

由于使用了复合主键，我需要用超过 1 个字段的信息来偏移页面。

这是一个应该在 PostgreSQL 中工作的示例 SQL(获取第 2 页):

SELECT * FROM table WHERE (Id, SeqNo) > (1, 4) AND Valid = 'Y' ORDER BY Id, SeqNo LIMIT 3;

我如何在 oracle 中实现相同的目标？

Answer 1

使用row_number() 解析函数和ceil 算术函数。算术函数不会对性能产生负面影响，row_number() over (order by ...) 表达式会自动对数据进行排序，而无需考虑插入顺序，也无需添加额外的 主查询的 order by 子句。所以，考虑:

select Id,SeqNo,
       ceil(row_number() over (order by Id,SeqNo)/3) as page
  from tab
 where Valid = 'Y';

附言它也适用于 Oracle 11g，而 OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY 仅适用于 Oracle 12c。

Demo