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

There's the cost of having a function on a column so the optimizer cannot use an index on tht column.

>
> 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

It almost looks to me like the table was denormalized a little too much. I do not know it your choice will be faster. The decode is shorter easier to read for procedural programmers and your version is easy to read for mathematically oriented programmers (gotta love boolean algebra!). I haven't thought through the implications if "chg_party" is NULL, but maybe it has a NOT NULL constraint? If not, there might be a difference in the result sets.

If you want to know the performance difference, then you need to test both. Performance varies as a function both of how the query is written and of the data loaded in the tables.

   Ed Received on Thu Feb 01 2007 - 09:48:05 CST