请参考!这个没有什么原因能解释,只能说是oracle一个bug!
参照CBO一书原文如下!
create index t1_i1 on t1(n1, ind_pad, n2)
More on Range-based Tests
We took the easy option, and did a range-based test on the last column in the index. What
happens if we do a range-based test on an earlier column in the index? Try this, for example:
alter session set "_optimizer_skip_scan_enabled"=false;
select
/*+ index(t1) */
small_vc
from
t1
where
n1 between 1 and 3
and ind_pad = rpad('x',40)
and n2 = 2
;
Execution Plan (8.1.7.4)
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=264 Card=82 Bytes=4756)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=264 Card=82 Bytes=4756)
2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=184 Card=82)
Execution Plan (9.2.0.6 and 10.1.0.4)
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=264 Card=82 Bytes=4756)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=264 Card=82 Bytes=4756)
2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=184 Card=1633)
The alter session command is there for the benefit of 9i and 10g. The unhinted execution
plan was a full tablescan, but when I first put in an index hint, the optimizer insisted on using
the index skip scan mechanism, and in 10g if I then included the no_index_ss() hint, the index
was disabled and the plan went back to a tablescan (I would be inclined to call this behavior a
bug—it seems perfectly reasonable to me to say, “Use this index, but don’t do a skip scan,” but
it is possible that it’s the specified behavior).