I'm having a problem with a certain query.
The query used to go twice in the same huge table. To solve that problem I've used a decode on certain columns, but surprisingly
the query performance became worst. I was forced to use decode on 25 columns.
My question is:
Is it possible that a lots of decodes degradates the query performance?
Agree with Ishan ..
I've thought in some side cases DECODE supposedly performs better -however, there appears to be no evidence supporting this claim
In fact, this evidence here proves they are "about the same":
It could just be your query needs to take that time to get the information you are asking for. *shrug*
Post a more complete sample and we can comment
("We" - meaning "other people with more knowledge about this stuff than myself" )
it's very unlikely that DECODE would make any difference either way. Most likely, your statement performance isn't very stable and the degradation you saw was just a random fluke (e.g. due to less data being available via cache at that specific moment). Alternatively, it could be due to change in the query plan, but it has little to do with DECODE -- any change in query text causes it to be re-optimized, and it could have lead to a different plan because of change in statistics or application data.