Re: How to get query to use an index
- From: Tim Gorman <tim@xxxxxxxxx>
- To: "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 11 Apr 2006 15:22:21 -0600
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@xxxxxxxxx wrote:
> Why not consider using a concatenated index with both columns versus writing
> the SQL statement as a Union All
>
> Tim Gorman <tim@xxxxxxxxx> 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@xxxxxxxxxxx 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.
>>> >
>>> > --
>>> > //www.freelists.org/webpage/oracle-l
>>> >
>>> >
>
>
>
> Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates
> starting at 1¢/min.
Other related posts:
