Re: How to speed up the search with OR criteria

Sarek_at_cityscape.co.uk (G Martin) wrote:
>Alan Tai <alantai> wrote:
>
>>I wonder how to speed up the response time of SQL statements like " SELECT *
>>FROM table1 WHERE field1 LIKE 'A%' OR field2 LIKE 'A%';" and " SELECT COUNT(*)
>>FROM table1 WHERE field1 LIKE 'A%' OR field2 LIKE 'A%';".
>
>The 'Like' statement is the problem - it *never* uses an index. If you
>need to handle this sort of stuff regularly consider building your own
>hash key for 'field1', 'field2' and using '=' instead of 'like'
>

I suppose it could depend on your version of Oracle, but it is incorrect to say that Oracle *never* uses an index w/the LIKE operator. If the column is a single-column index, or a leading edge of a composite-column index, AND the condition is written on the model of

WHERE ... 'col' LIKE 'X%' ( not ...LIKE '%X' or ...LIKE '%X%' )

It is part of a bounded range search ( access path 10 in Concepts Manual, in the optimizer chapter ).

I believe this to be true for all of Oracle7.

DL Kray Received on Tue Feb 06 1996 - 00:00:00 CET