Oracle 使用 Order by 与 rownum SQL 优化案例一则

0    80    1

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

这是某客户AWR报告中的一个TOP SQL,执行时间8.8小时(也有执行12.7小时报错退出的情况)

SELECT *

FROM

(

SELECT rownum num ,t.*

FROM TI_BI_CAMPN_USERORDER_TEMP t

WHERE oper_type = '0' OR oper_type = '2' OR

(oper_type = '1' AND end_date <= add_months(trunc(sysdate, 'mm'), 1))

ORDER BY oper_time ASC

) c

WHERE c.num <= : 1;

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

img

表记录数280M,返回其中符合条件并按oper_time排序的500条记录。

看到上面sql monitor显示的执行计划,可能有人会生出疑问:

返回了表的大部分记录,为什么不做全表扫描,而是使用了比较差的索引扫描?

答案是:

因为修改了索引相关的两个系统参数,把索引的COST大大的降低了,让优化器认为走索引总是比全表扫描要好:

optimizer_index_caching = 95(默认值0)

optimizer_index_cost_adj = 3(默认值100)

这个SQL如何优化呢?

根据SQL monitor显示的信息及表的实际记录数,我们可以通过创建oper_time字段上的索引来优化:

create index idx_name on TI_BI_CAMPN_USERORDER_TEMP(oper_time);

预计创建索引后,该SQL的执行时间应该在1~2秒左右

小结:

order by 与 rownum 搭配使用时,可以创建谓词字段与order by字段上的联合索引(本例不可,因为有or和不对称的谓词条件);或者在大部分数据都符合条件的情况下,创建order by字段上的索引(本例),避免大结果集的排序。

系统默认参数一般不建议修改(内存参数、bug除外),因为很多性能测试是在默认参数条件下完成。

其他情况:

如果这个SQL执行频率低,也可以选择不创建索引,使用并行加全表扫描来提高响应速度,使用下面的hint:

SELECT *

FROM

(

SELECT /*+ full(t) parallel(4) */rownum num ,t.*

FROM TI_BI_CAMPN_USERORDER_TEMP t

WHERE oper_type = '0' OR oper_type = '2' OR

​ (oper_type = '1' AND end_date <= add_months(trunc(sysdate, 'mm'), 1))

ORDER BY oper_time ASC

) c

WHERE c.num <= : 1;

如果索引那两个系统参数是默认值,其中的full hint是可以去掉的。

当前使用的并行度是4,具体的并行度可以根据实际需要适当增减。

注意:11g的并行写法已经不要求加表名或别名。10g中加表名或别名的写法繁琐而且容易遗漏,抛弃了吧!

参考

参考:老虎刘谈SQL优化

标签:

头像

小麦苗

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

您可能还喜欢...

发表回复

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

14 − 7 =

 

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

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

  • 回到顶部
返回顶部