Re: SQL: Combine LIKE and IN statement?

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Thu, 12 Feb 2004 09:17:31 -0800
Message-ID: <1076606202.510109@yasure>

Dave wrote:

> Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0402111800.3d4a3768_at_posting.google.com>...
>

>>Bob <no_at_mail.com> wrote in message news:<sBuWb.531553$_x2.1127068_at_zonnet-reader-1>...
>>
>>>Can anyone tell me how to improve the following:
>>>
>>>Select tbl.city
>>>From tbl
>>>Where tbl.city like 'B%' or
>>>tbl.city like 'G%' or
>>>tbl.city like 'R%'
>>>
>>>I was trying:
>>>Where tbl.city like in ('B%', 'G%', 'R%')
>>>But apparently this is not the right way.
>>>
>>>
>>>Thanx,
>>>Bob
>>
>>Bob, is there an index on the city column?  How about posting the
>>explain plan for the statement so we can see how Oracle choose to
>>solve the query?  Are your running cost or rule based?  Are the
>>statistics current? etc....
>>
>>HTH -- Mark D Powell --

>
>
>
> I can see where this OR/LIKE thing would be an issue when the like
> criteria is more "like" '%X%'
>
> but in your example couldn't you just have done this...
>
> Select tbl.city
> From tbl
> Where substr(tbl.city, 1, 1) in ( 'B', 'G', 'R' )
>
> not sure if index will be used in this case...
>
> Dave

Be sure ... the index won't be used unless it is an FBI based on the substring.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Thu Feb 12 2004 - 11:17:31 CST