Outline不能正确使用的问题
今天同事遇到一个outline不能正确使用的问题问我。抽象描述类似于下面的问题
create outline ol3732308613_tmp for category GOOD_PLAN on
select /*+ index(a,a_idx) */ … from a;
生成outline后，explain plan select /*+ index(a,a_idx) */ … from a; 却发现没有走a_idx，而是选择了full table scan
我看了一下ol$hints表，发现其并没有使用index hint，而是使用了INDEX_RS_ASC，并且还有一个hint为OPTIMIZER_FEATURES_ENABLE(’9.2.0′)
数据库版本为10g，optimizer_features_enable为9.2.0。这样的话INDEX_RS_ASC这个hint不能被识别，因为其为10g所引入的。
在创建outline前加上alter session set optimizer_features_enable=’10.2.0.3′ 就可以了
这样OPTIMIZER_FEATURES_ENABLE(’9.2.0′) 被替换为OPTIMIZER_FEATURES_ENABLE(’10.2.0.3′)。INDEX_RS_ASC 可以被正确使用。
about INDEX_RS_ASC (from jonathanlewis’s blog)
Bonus: The index() hint tell Oracle to use an index, but does not specify whether that the usage should be a full scan or a range scan. Because of some changes in costing in 10g, there have been cases reported where the optimizer – after hinting – picked a full scan when a range scan would have been a more intelligent choice. Consequently Oracle Corp. has introduced three new hints in 10.2.0.3 to “disambiguate” the index use. These are: index_rs(), index_rs_asc(), index_rs_desc(), which make explicit demands for an index range scan. It’s probably a good idea to use this option in all future code.
index_rs ?
good improvement