Re: How to get query to use an index

From: Tim Gorman <tim_at_evdbt.com>
Date: Tue, 11 Apr 2006 15:22:21 -0600
Message-ID: <C0617A2D.35091%tim@evdbt.com>

Whatıs wrong with UNION-ALL?

He said it works the way he wants when he specifies only one column or the other; the UNION-ALL does exactly that.

He didnıt say anything about not being able to rewrite the query. If he canıt, then he should consider other options, like concatenated indexes. Personally, Iım skeptical that they will do the job; I have no doubts about UNION-ALL... on 4/11/06 2:39 PM, Paula Stankus at paulastankus_at_yahoo.com wrote:

> Why not consider using a concatenated index with both columns versus writing
> the SQL statement as a Union All
> 
> Tim Gorman <tim_at_evdbt.com> wrote:
>> Josh,
>> 
>> Try:
>> 
>>   


>>> SELECT cus.cusky, cus.cliky, cus.cus01frnm, cus.cus01lanm, cus.cusblad01,

>>>       cus.cusblad02, cus.cusblcity, cus.cusblstate, cus.cusblzip,

>>>       cus.cusblcntry, cus.cusad01, cus.cusad02, cus.cuscy, cus.cusst,

>>>       cus.cuszp, cus.cuscountry, cus.cus01email, cus.cushoph, cus.cus01wrph,

>>>       cus.wpa_change

>>> FROM cus_current_row cus
>>> WHERE cushoph LIKE SYS_CONTEXT ('MY_CTX_wpaods', 'phone')
>>> UNION ALL
>>> SELECT cus.cusky, cus.cliky, cus.cus01frnm, cus.cus01lanm, cus.cusblad01,
>>> cus.cusblad02, cus.cusblcity, cus.cusblstate, cus.cusblzip,
>>> cus.cusblcntry, cus.cusad01, cus.cusad02, cus.cuscy, cus.cusst,
>>> cus.cuszp, cus.cuscountry, cus.cus01email, cus.cushoph, cus.cus01wrph,
>>> cus.wpa_change
>>> FROM cus_current_row cus
>>> WHERE cus01wrph LIKE SYS_CONTEXT ('MY_CTX_wpaods', 'phone')

>> 
>> Hope this helps...
>> 
>> -Tim
>> 
>> 
>> on 4/11/06 2:13 PM, Josh Collier at collier_jw_at_comcast.net wrote:
>> 


>>> > Query is currently using a full tablescan

>>> > 

>>> > There are two indexes.

>>> > 

>>> > One on cus01wrph

>>> > One on cushoph

>>> >
>>> > If I just put in one or the other (either cusoph or cus01wrph) then the
>>> index
>>> > is
>>> > used.
>>> >
>>> > SELECT cus.cusky, cus.cliky, cus.cus01frnm, cus.cus01lanm, cus.cusblad01,
>>> > cus.cusblad02, cus.cusblcity, cus.cusblstate, cus.cusblzip,
>>> > cus.cusblcntry, cus.cusad01, cus.cusad02, cus.cuscy, cus.cusst,
>>> > cus.cuszp, cus.cuscountry, cus.cus01email, cus.cushoph,
>>> cus.cus01wrph,
>>> > cus.wpa_change
>>> > FROM cus_current_row cus
>>> > WHERE ( cushoph LIKE SYS_CONTEXT ('MY_CTX_wpaods', 'phone')
>>> > OR cus01wrph LIKE SYS_CONTEXT ('MY_CTX_wpaods', 'phone')
>>> > )
>>> >
>>> > Thanks for your help,
>>> >
>>> > Josh C.
>>> >
>>> > --
>>> > http://www.freelists.org/webpage/oracle-l
>>> >
>>> >

> 
> 
> 
> Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls.  Great rates
> starting at 1˘/min.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 11 2006 - 16:22:21 CDT