Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Cost of decode in the WHERE part
ian 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
Everything has a cost. What that cost is depends on your specific usage. Thus the need to test in development before promoting code to a test environment.
Personally I'd go for the DECODE. Untested I would expect it to be as or more efficient. It is certainly easier to read and maintain.
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Thu Feb 01 2007 - 10:32:16 CST