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

这是某客户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;

表记录数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优化