RE: How to get query to use an index
- From: "Josh Collier" <collier_jw@xxxxxxxxxxx>
- To: <paulastankus@xxxxxxxxx>, <tim@xxxxxxxxx>, "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 11 Apr 2006 14:10:22 -0700
I tried using a concatenated index with both columns. Still a FTS was the
result. If I remove the like and use an = . the result is a bitmap OR operation
that uses both indexes. its the Like that is causing the issue.
Josh C.
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Paula Stankus
Sent: Tuesday, April 11, 2006 1:40 PM
To: tim@xxxxxxxxx; 'oracle-l'
Subject: Re: How to get query to use an index
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
<http://us.rd.yahoo.com/mail_us/taglines/postman7/*http://us.rd.yahoo.com/evt=39
666/*http://beta.messenger.yahoo.com> rates starting at 1¢/min.
Other related posts:
