4 Replies Latest reply on Aug 16, 2013 6:49 PM by Nikolay Savvinov

    Decode performance problem


      Good Afternoon,



      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?

        • 1. Re: Decode performance problem


          use case instead of decode. it will help to improve the performance.

          • 2. Re: Decode performance problem

            Not really.


            It is faster, may be in nano seconds. There is no significant performance related difference between CASE or DECODE. They are virtually same, but yes CASE is much more stronger than DECODE in other areas. But peformance, NO NO NO.



            • 3. Re: Decode performance problem
              Greg Spall

              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":


              Ask Tom "better performance - case or decode"


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

              • 4. Re: Decode performance problem
                Nikolay Savvinov



                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.



                Best regards,