Home » SQL & PL/SQL » SQL & PL/SQL » decode or if else (oracle 10 g)
decode or if else [message #421208] Sat, 05 September 2009 17:48 Go to next message
bond007
Messages: 58
Registered: March 2009
Member
Hi friends,
I have a procedure which uses more decode functions init.
is it good to use decode rather than if else ?

because the existing query takes alongtime to excute. I am thinking of replacing decode with " IF ELSIF" statments.

Need your suggestions frinds in this regard
Re: decode or if else [message #421211 is a reply to message #421208] Sat, 05 September 2009 20:36 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
Redundancy is the best way to teach idiots.
Redundancy is the best way to teach idiots.
Redundancy is the best way to teach idiots.
Redundancy is the best way to teach idiots.

Then again, maybe above is not true.

http://www.orafaq.com/forum/m/420246/136107/#msg_420247

http://www.orafaq.com/forum/m/416435/136107/#msg_416436

http://www.orafaq.com/forum/m/413598/136107/#msg_413599

It would not take much effort for you to actually run a benchmark test to see the impact of decode or not.
Given that both DECODE & IF THEN ELSE run at CPU speed, while getting data from disk runs 100 to 1000 times slower,
I doubt you can measure any difference.

[Updated on: Sat, 05 September 2009 20:37]

Report message to a moderator

Re: decode or if else [message #421216 is a reply to message #421211] Sun, 06 September 2009 00:21 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
BlackSwan is once again in a bad mood. But once again he is also correct.

There may be someone who has a general answer to your question. But in the end the only way you will really know, is to build your own test cases and try them out.

Before the inclusion of CASE in SQL, the main advantage (or disadvantage) was that DECODE would work inside a select statement and thus was about the only kind of IF you could do inside a SQL statement. Now with CASE however, you can build all sorts of if then else logic inside SQL.

I am an old hat so I tend to still use DECODE. Only because old habits are hard to break.

To repeat what BlackSwan said
To repeat what BlackSwan said
To repeat what BlackSwan said

You should build your own test cases and find out. Please post what you find out so the rest of us can reference it later. You should test the following three alternatives (however you plan to do it)...

IF THEN ELSE
DECODE
CASE

Good luck, Kevin
Re: decode or if else [message #421218 is a reply to message #421208] Sun, 06 September 2009 00:35 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
Any benchmark test results may be impacted by row cache in Oracle or OS file cache or by context switch between SQL & PL/SQL engines; more so by the former than the later.
Re: decode or if else [message #421242 is a reply to message #421218] Sun, 06 September 2009 13:27 Go to previous messageGo to next message
bond007
Messages: 58
Registered: March 2009
Member
Thanks Kevin for your help . But I did not understand why black swan says "Redundancy is the best way to teach idiots".
Any way thanks a lot for your help
Re: decode or if else [message #421366 is a reply to message #421208] Mon, 07 September 2009 21:14 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
sometimes he is just that way. He is a good sort so do let it bother you. After all, he did take the time to participate even if his remarks did have extra baggage in them.

See you, Kevin
Previous Topic: Trigger Problem
Next Topic: Creating primary key for schema FOUT using sys
Goto Forum:
  


Current Time: Sat Oct 01 09:13:57 CDT 2016

Total time taken to generate the page: 0.06359 seconds