Re: How to get query to use an index

From: Paula Stankus <paulastankus_at_yahoo.com>
Date: Tue, 11 Apr 2006 13:39:47 -0700 (PDT)
Message-ID: <20060411203947.29719.qmail@web33201.mail.mud.yahoo.com>

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

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

Received on Tue Apr 11 2006 - 15:39:47 CDT