RE: How to get query to use an index

From: Josh Collier <collier_jw_at_comcast.net>
Date: Tue, 11 Apr 2006 14:10:22 -0700
Message-Id: <20060411211025.835642E5DA3@turing.freelists.org>

I tried using a concatenated index with both columns. Still a FTS was the result. If I remove the like and use an = . the result is a bitmap OR operation that uses both indexes. its the Like that is causing the issue.

Josh C.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Paula Stankus
Sent: Tuesday, April 11, 2006 1:40 PM
To: tim_at_evdbt.com; 'oracle-l'
Subject: Re: How to get query to use an index

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 <http://us.rd.yahoo.com/mail_us/taglines/postman7/*http://us.rd.yahoo.com/evt=39 666/*http://beta.messenger.yahoo.com> rates starting at 1¢/min.

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

Received on Tue Apr 11 2006 - 16:10:22 CDT