HOW decode function affects the performance? Can i use more than one decode in a single sql statement?
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.
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?
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).
No one has followed this question yet.