Re: How to speed up the search with OR criteria

In article <4f04jo$n6p_at_news.cityscape.co.uk>, G Martin <Sarek_at_cityscape.co.uk> writes
>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 was suprised to here this, so I discussed it with a coleague. He reckons that the COST based optimiser may be the problem. Aparently the database block size may sometime swing a full table scan.

It may be worth trying a hint to use the RULE based optimizer.

Clive Bostock

All opions expressed are those of my
own and not my employer. Received on Sat Feb 10 1996 - 00:00:00 CET