Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Cost of decode in the WHERE part

Re: Cost of decode in the WHERE part

From: John K. Hinsdale <hin_at_alma.com>
Date: 2 Feb 2007 12:13:09 -0800
Message-ID: <1170447189.469374.75110@s48g2000cws.googlegroups.com>


On Feb 1, 3:04 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> If there is an index on
> the CHG_PARTY column, the DECODE statement will prevent Oracle from
> using the index.

True, but if Ian does

    CREATE INDEX i_a_or_b_sub_num ON ians_table     (DECODE(upper(chg_party),'A',a_sub_num,b_sub_num))

while also using the WHERE clause form:

    and DECODE(upper(chg_party),'A',a_sub_num,b_sub_num) = t.telephony_id

my tests, using a similar expression, indicate that Oracle will detect and use the above function-based index for filtering & joining.

This is of course assuming a scan of the table containing the columns (CHG_PARTY, A_SUB_NUM, B_SUB_NUM) would be costly. I'm also assuming these three columns come from the same table, which is required for the above index to work (but that is not obviously so without seeing the whole SQL).

HTH
John Hinsdale Received on Fri Feb 02 2007 - 14:13:09 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US