关于oracle模式下sql分页语法的问题

应用开发者专区
#1

现有一个老的项目要从oracle数据库切换到OB，想请教下各位大佬，OB的oracle模式下分页语法是保持和之前oracle一样用rownum伪列分页好，还是用limit、offset分页？
网上资料众说纷纭，有的说rownum效率高，有的说用limit offset效率高，实际啥情况？
项目中基本没有百万级的数据查询，延用oracle的rownum分页语法会有性能问题吗

#3

oracle 早期版本的分页方法 利用 rownum 写两层嵌套循环，这个写法很繁琐。而 mysql 的 limit offset 写法则非常简洁。
oracle 12c 后 oracle 也推出类似的写法 offset N rows fetch next M rows 。
oceanbase 的 oracle 租户兼容了 oracle 的两种分页方法。
从sql 编写上看，肯定是用新的分页方法更方便。对比执行计划，在 OB 4.2.5 版本里，也是新的分页方式性能更好。

  • 老的分页方式
explain SELECT * from (SELECT t.*, rownum rn FROM (SELECT o_w_id,o_d_id,o_id,o_c_id,o_cnt FROM bmsql_oorder o ORDER BY o_c_id) t WHERE rownum <=20) WHERE rn > 10;

|0 |COUNT                  |     |10      |1982180     |
|1 |└─SUBPLAN SCAN         |VIEW1|10      |1982180     |
|2 |  └─LIMIT              |     |10      |1982180     |
|3 |    └─TOP-N SORT       |     |20      |1982180     |
|4 |      └─TABLE FULL SCAN|O    |12660010|1160214     |
========================================================
Outputs & filters:
-------------------------------------
  0 - output([VIEW1.T.O_W_ID], [VIEW1.T.O_D_ID], [VIEW1.T.O_ID], [VIEW1.T.O_C_ID], [VIEW1.T.O_CNT], [rownum() + cast(cast(FLOOR(10), BIGINT(-1, 0)), NUMBER(-1,
       -1))]), filter(nil)
  1 - output([VIEW1.T.O_W_ID], [VIEW1.T.O_D_ID], [VIEW1.T.O_ID], [VIEW1.T.O_C_ID], [VIEW1.T.O_CNT]), filter(nil), rowset=256
      access([VIEW1.T.O_W_ID], [VIEW1.T.O_D_ID], [VIEW1.T.O_ID], [VIEW1.T.O_C_ID], [VIEW1.T.O_CNT])
  2 - output([O.O_W_ID], [O.O_D_ID], [O.O_ID], [O.O_C_ID], [O.O_CNT]), filter(nil), rowset=256
      limit(cast(cast(cast(FLOOR(20), BIGINT(-1, 0)), NUMBER(-1, -1)) - cast(cast(FLOOR(10), BIGINT(-1, 0)), NUMBER(-1, -1)), BIGINT(-1, 0))), offset(cast(FLOOR(10),
       BIGINT(-1, 0)))
  3 - output([O.O_W_ID], [O.O_D_ID], [O.O_ID], [O.O_C_ID], [O.O_CNT]), filter(nil), rowset=256
      sort_keys([O.O_C_ID, ASC]), topn(cast(cast(cast(cast(cast(FLOOR(20), BIGINT(-1, 0)), NUMBER(-1, -1)) - cast(cast(FLOOR(10), BIGINT(-1, 0)), NUMBER(-1,
       -1)), BIGINT(-1, 0)), NUMBER(-1, -1)) + cast(cast(FLOOR(10), BIGINT(-1, 0)), NUMBER(-1, -1)), BIGINT(-1, 0)))
  4 - output([O.O_W_ID], [O.O_D_ID], [O.O_ID], [O.O_C_ID], [O.O_CNT]), filter(nil), rowset=256
      access([O.O_W_ID], [O.O_D_ID], [O.O_ID], [O.O_C_ID], [O.O_CNT]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([O.__pk_increment]), range(MIN ; MAX)always true
  • 新的分页方式
explain select o_w_id,o_d_id,o_id,o_c_id,o_cnt from bmsql_oorder ORDER BY o_c_id offset  10 rows fetch next 10 rows only;

===========================================================
|ID|OPERATOR           |NAME        |EST.ROWS|EST.TIME(us)|
-----------------------------------------------------------
|0 |LIMIT              |            |10      |1982180     |
|1 |└─TOP-N SORT       |            |20      |1982180     |
|2 |  └─TABLE FULL SCAN|BMSQL_OORDER|12660010|1160214     |
===========================================================
Outputs & filters:
-------------------------------------
  0 - output([BMSQL_OORDER.O_W_ID], [BMSQL_OORDER.O_D_ID], [BMSQL_OORDER.O_ID], [BMSQL_OORDER.O_C_ID], [BMSQL_OORDER.O_CNT]), filter(nil), rowset=256
      limit(cast(FLOOR(10), BIGINT(-1, 0))), offset(cast(FLOOR(10), BIGINT(-1, 0)))
  1 - output([BMSQL_OORDER.O_W_ID], [BMSQL_OORDER.O_D_ID], [BMSQL_OORDER.O_ID], [BMSQL_OORDER.O_C_ID], [BMSQL_OORDER.O_CNT]), filter(nil), rowset=256
      sort_keys([BMSQL_OORDER.O_C_ID, ASC]), topn(cast(cast(cast(FLOOR(10), BIGINT(-1, 0)), NUMBER(-1, -1)) + cast(cast(FLOOR(10), BIGINT(-1, 0)), NUMBER(-1,
       -1)), BIGINT(-1, 0)))
  2 - output([BMSQL_OORDER.O_W_ID], [BMSQL_OORDER.O_D_ID], [BMSQL_OORDER.O_ID], [BMSQL_OORDER.O_C_ID], [BMSQL_OORDER.O_CNT]), filter(nil), rowset=256
      access([BMSQL_OORDER.O_W_ID], [BMSQL_OORDER.O_D_ID], [BMSQL_OORDER.O_ID], [BMSQL_OORDER.O_C_ID], [BMSQL_OORDER.O_CNT]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([BMSQL_OORDER.__pk_increment]), range(MIN ; MAX)always true
1 个赞
#4

学习了，oracle 优化了这个都不知道。 :+1:

#5

分页性能上可能会出问题吧

#6

使用rownum有性能问题？offset在深分页情况下也要跳过大量行，这个说是也有性能问题

#7

大佬是推荐用offset fetch？offset在深分页情况下需要跳过大量行，这个会有性能问题吗？官方文档上说是offset跳过大量数据性能会下降

#8

个人对oracle的分析 不代表官方 非ob oracle模式下 自己可以在ob oracle模式下 测试一下
1、伪列分页
select column_lists from
(select rownum as rn,A.* from
(select column_lists from table_name where col_1=:b0) A where rownum<=:b2
) where rn>:b1;
2、使用row_number() over分析函数，
select * from (select a.*, row_number() over (order by empno) as rn from emp a) where rn between 5 and 10;`
3、oracle 12c 中使用了简洁的offset 语法
select * from emp order by empno offset 4 rows fetch next 5 rows only;
我记得这个内部是row_number() over()的改写 不过这个排序要做索引 offset的值尽量要大致确定好对于非常大的OFFSET值，可能需要遍历许多无关的行才能达到OFFSET的位置

这几种方式分页 其实到了百万行 还分页其实性能也会有折扣 oracle12C之前没有标准的sql分页 offset这个写法 比较简洁 。

#9

这个知识牵扯ob的企业版，你可以咨询企业版客户经理，另外，我们欢迎你使用社区版，并在论坛/社群中分享你对社区版本的想法、经验和问题，与其他社区成员共同交流。