Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Cost of decode in the WHERE part
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