Re: How to speed up the search with OR criteria

Alan Tai <alantai> wrote-écrivait:

>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%';".
>I tried to create seperative or compositive indexes for field1 and field2. But
>no matter what kind of indexes I created, I found that Oracle didn't use the
>index. Even though I use hint in the statement.
 

>I look forward any suggestion from anyone.
 

>Thanks.
>Alan.
>--------------------------------------------------------------------------
>Alan Tai | alantai_at_multiactive.com
>New Media Promotions | 1095 West Pender St., 10th Floor
>MultiActive Technologies | Vancouver, BC V6E 2M6
> | PH: 604-895-8282 FAX: 683-2668
>--------------------------------------------------------------------------

As soon you are using the like command, generally it won't pass thru the indexes. You will have a full table scan. Use the tk-proof or the explain plan tools to know exactly where your sql goes.

If you use the like instruction and you will receive frequently the % alone I suggest you to use that syntax :

where ( table.column1 = '%'

               or table.colunm1 like abcde
             )

That way, the first condition of the where clause = % if true will eliminate the full table scan of like %. That will speed up the query.

Michel Lapierre Received on Mon Feb 05 1996 - 00:00:00 CET