Using a range scan rather skip scan
564035Jan 27 2010 — edited Jan 27 2010Hi,
In oracle 10g (10.2.0.1.0)
I have a table with a compound index based on 2 columns.
Ex. :
table1 (p1 number , d1 date, c2 varchar2(10))
create index table1x on table1(d1, c2)
in the query :
select p1
from table1
where d1 between sysdate-10 and sysdate
and c2='x'
IN this case, the optimizer selected to use an index skip scan (to use the second column which is c2) instead of using an index range scan on column d1.
I tried to use the hint NO_INDEX_SS (table1, table1x) so NO index was used and the optimzer went for a full table scan.
Is there a way to force the optimzer to use the range scan on d1 instead or skip can on C1 ?
Thanks ...
AK