Re: SQL index use

>We've got large table (270000 records and 30
>columns) in 8i. There are 3 indexes. Is there any
>possibiity to force oracle to use index I want him
>to use?
>
>statement:
> select col1, col2, col3, col4 from
> table_name where col1 like 'jac%' and
> col2 like 'bar%' and col3 like '00032%';
>
>IT ALWAYS USES COL3 AS INDEX why???????????
>[col1 is PK, other col's are indexed]
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
>
>

In addtion to Thomas Kytes' index hint, you might also consider concatentating the 3 indexes (I assume they are 1-to-1 with the columns) into one index. That would allow the determination of if a row meets the criteria to be done completely on the index, and not requires a retrival of the actual data. This would depend on how many different conditions you use to find a record (Oracle can use the leading part of an index, and ignore trailing parts)

Dan Hekimian-Williams Received on Mon Oct 04 1999 - 04:44:53 CEST