question

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?

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

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).

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