Oracle rownum应用系列之分页查询

0    61    1

👉 本文共约1441个字,系统预计阅读时间或需6分钟。

rownum这个伪列在SQL中用途广泛,归纳起来大致有3总用法:

1、分页查询 rownum<= :b1 and RN>:b2 (rownum不能>)

2、rownum<= :b1

3、rownum=1 或 rownum<=1 ,也是rownum<=:b1的一种特例

我们接下来分别会找相关的优化案例来进行分析。

今天先谈谈简单的分页查询写法,虽然是老生常谈,但是因为在很多客户的大型系统中都发现了低效写法,还是再重新加强一下,如果能做成开发规范最好:

低效的写法:

select column_lists from

(select rownum as rn,A.* from

(select column_lists from table_name where col_1=:b0 order by col_2) A

) where rn<=:b2 and rn>:b1;

高效的写法,注意红色部分内容的位置:

select column_lists from

(select rownum as rn,A.* from

(select column_lists from table_name where col_1=:b0 order by col_2) A

where rownum<=:b2

) where rn>:b1;

原理:

低效写法需要将内层的结果集全部排序,再从中取出需要的部分;而高效写法只需要获取排序后<=:b2部分的结果就可以了。

一般分页查询访问前面部分页面的几率较大,内层查询的结果集越大,性能差距越明显。

如果是访问分页的最后部分的页面,基本上就没什么差别了。

oracle 12c 中使用了简洁的offset 语法,其实是使用了分析函数row_number()在内部做了改写,效率也很高。当前的主流写法还是上面使用rownum伪列的方法。

思考一下

这样的分页查询语句,应该创建怎样的索引?

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

昨天的思考题,老虎刘的建议是:

创建col_1和col_2字段上的联合索引,避免排序,提高效率。联合索引的字段顺序不能错,order by的字段要放在联合索引的最后。如果是order by desc,正常情况优化器会自动使用index descending扫描方式,不需要建索引的时候加desc 。也有sql复杂的时候优化器没有使用index descending扫描方式,则可以用index_desc来纠正。

今天刚好遇到一个分页查询的问题SQL,简化如下:

SELECT *

FROM (

SELECT

......

,ROWNUM RNSTART

FROM S_SRV_REQ SSR

WHERE SSR.ACT_OPEN_DT + 1 / 3 >= SYSDATE - 60 AND

ROWNUM <= 10 ORDER BY SSR.CREATED DESC

) T

WHERE T.RNSTART >= 1;

这个SQL有两个问题:

1、分页查询的写法有问题,rownum 与 order by并列同时出现在同一个where后面,这种写法得到的结果可能是乱序(除非使用CREATED字段上索引的降序扫描,但是如果该索引不可用,得到的结果就不对了),需要按照上一篇文章的写法进行改写。

2、谓词条件SSR.ACT_OPEN_DT + 1 / 3 >= SYSDATE - 60过滤性很好,而且存在ACT_OPEN_DT字段上的索引,但是因为字段上存在表达式 “+1 / 3“,导致索引不能使用,需要改写sql,将 SSR.ACT_OPEN_DT + 1 / 3 >= SYSDATE - 60 改成SSR.ACT_OPEN_DT >= SYSDATE - 60 - 1 / 3。

注:

因为谓词条件不是等值条件(昨天的sql是col_1= :b1,今天是col_x >=),这个sql如果创建ACT_OPEN_DT和CREATED联合索引是没有意义的,这种情况还是只会使用索引的第一个字段,无法避免排序。

参考

参考:老虎刘谈SQL优化

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

1 × 1 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部