Re: How to get query to use an index

From: Paula Stankus <paulastankus@xxxxxxxxx>
To: tim@xxxxxxxxx, 'oracle-l' <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 11 Apr 2006 13:39:47 -0700 (PDT)

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&cent;/min.

Follow-Ups:
- RE: How to get query to use an index
  - From: Josh Collier
- Re: How to get query to use an index
  - From: Tim Gorman

References:
- Re: How to get query to use an index
  - From: Tim Gorman

Re: How to get query to use an index

Other related posts: