x

Decode performance

HOW decode function affects the performance? Can i use more than one decode in a single sql statement?

more ▼

asked Jan 13, 2010 at 01:34 PM in Default

avatar image

Shilp
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

How does it affect performance? That will depend on how you use it. You might create an SQL statement with and without it and do analysis or time your query execution for comparison.

Can you use it more than once? Yes you can. You can use it in more than one column expression and you can also nest them. In fact, someone showed me an expression that had about 12 nested DECODEs. It was fairly ugly. You might try CASE instead of DECODE, some think it's easier to read/understand. Of course, 12 nested CASE expressions would be ugly, too.

Update: I read your comment. If you're using DECODE when qualifying or modifying a limited number of rows, it shouldn't adversely affect performance too much. However, if it's part of your join clause, all bets are off. Performance with DECODE is going to depend on how you use it.

I'd recommend creating two versions of your SQL, one with DECODE and one simplified without it. For test purposes, it's probably OK if it doesn't do quite the same thing, as long as the number of rows affected will be reasonably close to the real query (within, say, 20%). Run both, a couple times each, and seee what the differences are in time required and resources consumed.

Update: I read your other comment. If I understand what you're trying to do, then I think using DECODE (or CASE) is a reasonable way to approach the problem.

When you ask, "How does it affect performance?," the answer is, "Compared to what?" If you're changing rows to columns... let's say, a table of information by quarters:

RevByQ
Year  Q   Rev
2008  q1  12
2008  q2  14
2008  q3  16
2008  q4  18
2009  q1  13
2009  q2  15
2009  q3  17
2009  q4  19

into a summary for the year:

Year    q1  q2  q3  q4
2008    12  14  16  18
2009    13  15  17  19

then there's not too many good ways to do that and a DECODE (case) to populate the appropriate quarter column is reasonable. There is another way, though...

select
  rbq1.year,
  rbq1.rev,
  rbq2.rev,
  rbq3.rev,
  rbq4,rev
from RevByQ rbq1
join  RevByQ rbq2
on  rbq1.year = rbq2.year
join  RevByQ rbq3
on  rbq1.year = rbq3.year
join  RevByQ rbq4
on  rbq1.year = rbq4.year

but I don't believe this is going to be faster than your solution.

more ▼

answered Jan 13, 2010 at 01:48 PM

avatar image

KillerDBA
1.5k 9 11 14

Thank you so much KillerDBA.My requirement is to transform rows into columns, so i have used around 15 decode functions in a single sql.Also because all the oracle compiler doesn't support CASE , i had to use DECODE.So i am worried will it effect the performance while running in production?

Jan 13, 2010 at 02:39 PM Shilp

It seems that the row to column transformation I described can also be done with a PIVOT but I haven't yet figured out how to use that.

Feb 01, 2010 at 01:46 PM KillerDBA
(comments are locked)
10|1200 characters needed characters left

Thank you so much KillerDBA.My requirement is to transform rows into columns, so i have used around 15 decode functions in a single sql.Also because all the oracle compiler doesn't support CASE , i had to use DECODE.So i am worried will it effect the performance while running in production?

more ▼

answered Jan 13, 2010 at 03:36 PM

avatar image

Shilp
11 1 1 1

What version of Oracle do you have? CASE is supported in 10 and 11, for sure. I think it was also in 9 but I'm not sure.

Jan 13, 2010 at 05:05 PM KillerDBA
(comments are locked)
10|1200 characters needed characters left

IMHO the compiler now supports CASE. But you might not have that version of Oracle.

more ▼

answered Feb 01, 2010 at 09:14 AM

avatar image

Lars
21

(comments are locked)
10|1200 characters needed characters left

I'm pretty sure that from Oracle 9i on, the PLSQL and SQL compiler were unified, meaning that the CASE should compile just fine. What error are you getting?

Also, the reason to use CASE as opposed to DECODE, is that CASE has lazy evaluation, as opposed to DECODE which will evaluate all expressions before proceeding. Consider if you had something like this:

select decode(ename, 'SMITH', 'That''s me', some_expensive_string_to_calculate)
from dual;

as opposed to:

select CASE ename 
           when 'SMITH' then 
                 'That''s me'
           else
                  some_expensive_string_to_calculate
       END
from dual;

This means that you're saving the time it would otherwise take to call your function (just to throw away the result).

more ▼

answered Feb 02, 2010 at 02:41 PM

avatar image

Dwayne King
71 1 2

(comments are locked)
10|1200 characters needed characters left

What Dwayne King writes about DECODE not using lazy evaluation is actually not correct, as proven here

more ▼

answered Jan 18, 2011 at 11:24 AM

avatar image

Sren Boisen
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1155
x441
x312
x59

asked: Jan 13, 2010 at 01:34 PM

Seen: 5753 times

Last Updated: Jan 17, 2010 at 04:38 PM

Copyright 2017 Redgate Software. Privacy Policy