Re: How to get query to use an index

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
>
>

--
http://www.freelists.org/webpage/oracle-l