Re: Cost of decode in the WHERE part

On Feb 1, 9:08 am, "ian" <ipel..._at_yahoo.com> wrote:
> Hi All;
>
> Is there any experience on the cost of having DECODE in the where part
> of a select statement on this list Please?
>
> EG
> and DECODE(upper(chg_party),'A',a_sub_num,b_sub_num) = t.telephony_id
>
> I would have done somethong like :-
> and ( ( a_sub_num = t.telephony_id and ( chg_party = 'A' or
> chg_party = 'a' ) )
> or ( b_sub_num = t.telephony_id and ( chg_party != 'A' and
> chg_party != 'a' ) )
> )
>
> The select is on a wide long table that will have to be scanned from
> other parts of the SQL.
>
> Regards
> Ian

There is no straight answer to your question. If there is an index on the CHG_PARTY column, the DECODE statement will prevent Oracle from using the index. If the CHG_PARTY column only contains two values (A and B, or a and b), the index on the column probably will not be very helpful in terms of execution performance, so not being able to use the index may not be a big loss. The DECODE statement will force Oracle to use a particular execution plan. Oracle may chose to rewrite your SQL statement to look something like this: SELECT
  *
FROM
  ...
WHERE
  A_SUB_NUM = T.TELEPHONY_ID
  AND CHG_PARTY = 'A'
UNION
SELECT
  *
FROM
  ...
WHERE
  A_SUB_NUM = T.TELEPHONY_ID
  AND CHG_PARTY = 'A'
UNION
SELECT
  *
FROM
  ...
WHERE
  B_SUB_NUM = T.TELEPHONY_ID
  AND CHG_PARTY <> 'A'
UNION
SELECT
  *
FROM
  ...
WHERE
  B_SUB_NUM = T.TELEPHONY_ID
  AND CHG_PARTY <> 'a';

Now the question. Will your version require four full table scans of the table, while the DECODE version only required one?

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Feb 01 2007 - 14:04:58 CST