这是某客户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中加表名或别名的写法繁琐而且容易遗漏，抛弃了吧！
各位网友如果有什么意见、建议、问题都可以与老虎刘沟通。
老虎刘的文章都是原创，欢迎大家转发。
