RE: How to get query to use an index (...what's wrong with the union-all, maybe nothing...)

From: Josh Collier <collier_jw_at_comcast.net>
Date: Wed, 12 Apr 2006 10:57:00 -0700
Message-Id: <20060412175702.C51142E6E86@turing.freelists.org>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham
Sent: Wednesday, April 12, 2006 8:30 AM
To: tim_at_evdbt.com; 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]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_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 Wed Apr 12 2006 - 12:57:00 CDT