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:
into a summary for the year:
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...
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?
answered Jan 13, 2010 at 03:36 PM
IMHO the compiler now supports CASE. But you might not have that version of Oracle.
answered Feb 01, 2010 at 09:14 AM
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:
as opposed to:
This means that you're saving the time it would otherwise take to call your function (just to throw away the result).
answered Feb 02, 2010 at 02:41 PM