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: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 01 Feb 2007 08:32:16 -0800
Message-ID: <1170347532.27917@bubbleator.drizzle.com>


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.org
Received on Thu Feb 01 2007 - 10:32:16 CST

Original text of this message

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