Re: LIKE performance

On Fri, 25 Feb 2005 09:25:28 +1000, lh wrote:

> I have a table with about 30000 rows, one column is a varchar2(90). I'm
> doing a LIKE 'XXX%' query and its not using an index.
>
> I tried querying with substr(col, 1, 10) = 'XXXXXXXXXX' with an index on
> substr(col, 1, 10). It doesn't use the index, even with a hint.
>
> Whats the best way to get good performance out of this type of query.
>
> TIA
>
>
> LH

Depending on the version of Oracle, and the frequency of the specific query, you might consider a funcion based index.

However, you leave us with far too little info to go beyond smarmy guesses.

FGB Received on Thu Feb 24 2005 - 17:29:49 CST