RE: How to get query to use an index (...what's wrong with the union-all, maybe nothing...)
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <tim@xxxxxxxxx>, "Oracle-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 12 Apr 2006 11:29:31 -0400
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:
