RE: How to get query to use an index (...what's wrong with the union-all, maybe nothing...)
- From: "Josh Collier" <collier_jw@xxxxxxxxxxx>
- To: "'Oracle-L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 12 Apr 2006 10:57:00 -0700
Hi,
The sys_context could have leading wild cards, trailing wild cards, or infact a
wild card in the middle. I've convinced the developer to use an = operator
thanks!
Josh C.
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Mark W. Farnham
Sent: Wednesday, April 12, 2006 8:30 AM
To: tim@xxxxxxxxx; Oracle-L
Subject: RE: How to get query to use an index (...what's wrong with the
union-all, maybe nothing...)
Is there a guarantee that the matching LIKEs on the two disparate columns are
disjoint? If so, it seems to me that the UNION-ALL would be optimal. ORs on
components of concatenated indexes still have to scan the entire index, right?
Now I am a bit perplexed about what the possible wildcards are in the return
from SYS_CONTEXT, and how the optimizer could plan for anything but a full index
scan if the sys_context could return a leading wildcard. And how would the
optimizer know it couldn?t? Soooo? what use of the indexes is the plan
reporting? If they are scans, then the union all might still be a win if the sum
of the leaf blocks from the two indexes plus the table blocks required for
additional column values for the selected rows is less than the total of the
blocks in the table. Of course if the aforementioned guarantee is not
guaranteed, then you?d have to switch to UNION and tack on the cost of the full
row projection duplicate rejection.
Regards,
mwf (guilty of not having read the entire thread)
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of Tim Gorman
Sent: Tuesday, April 11, 2006 5:22 PM
To: 'oracle-l'
Subject: Re: How to get query to use an index
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:
