3 Replies Latest reply on Aug 5, 2010 12:08 PM by 717817

    CASE or DECODE - what is faster?

    pawel
      when i use them in select clause with varchar2 or number datatype? (Oracle 10.2 EE)

      regards
        • 1. Re: CASE or DECODE - what is faster?
          Karthick2003
          I think there would be not much of difference. They are generally not consider for Performance when using in the Select List of a Select Query.
          • 2. Re: CASE or DECODE - what is faster?
            Lokanath Giri
            Check this thread
            which is the best decode or case

            No much difference, CASE looks better
            SQL> ed
            Wrote file afiedt.buf
            
                DECLARE
                   l_start NUMBER;
                   l_PRED VARCHAR2(1) :='C';
                   X VARCHAR2(1);
                 BEGIN
                   l_start:=DBMS_UTILITY.get_time;
                  FOR i in 1..100000 Loop
                   SELECT DECODE( l_PRED,'A',1,'B+',2,'C+',3,'C',1,0) INTO X FROM DUAL;
                  END LOOP;
                 DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.get_time-l_start);
               END;
            /
            307
            
            PL/SQL procedure successfully completed.
            
            SQL>  DECLARE
                 l_start NUMBER;
                  l_PRED VARCHAR2(1) :='C';
                  X VARCHAR2(1);
                BEGIN
                  l_start:=DBMS_UTILITY.get_time;
                 FOR i in 1..100000 Loop
                SELECT ( CASE 'C'
                             WHEN 'A'  THEN  1
                             WHEN 'B+' THEN  2
                             WHEN 'C+' THEN  3
                             WHEN 'C'  THEN  1
                             ELSE            0
                         END) INTO X
                           FROM DUAL;
               END LOOP;
                DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.get_time-l_start);
               END;
              /
            292
            • 3. Re: CASE or DECODE - what is faster?
              717817
              Hi,

              This helped me in understanding the difference between case and decode

              Quoting from an expert amoungst experts Billy Verreynne. here is what he says:

              From what i know, in case of multiple condition checking, Case is simpler to write when compared to Decode. Also Decode can not be used in PL/SQL code where as CASE is possible.

              There is very little performance difference between CASE and DECODE on the same platform. One has to run 100's of 1000's of iterations to see a difference, and even then it is debatable of whether that difference is just due to the CASE vs DECODE.

              There seems to be a difference in performance between CASE and DECODE depending on the type of CPU. On some CPU architecture a DECODE will seem to be just slightly faster. On others, the CASE will seem to be slightly faster.

              The performance difference is so slight that it makes very little sense in using that as primary criteria for whether to use CASE or DECODE. So unless you're calling this statement from a very tight loop doing millions of iterations, the decision should rather be which one, CASE or DECODE, best suits the need.

              Wrong question really.. unless you writing a very tight loop doing 100's of 1000's of iterations using that type of conditional structure. And even then performance difference will not be that significant.

              The right question to ask is which one is more flexible and allows for the programmer that comes after you to read and understand and maintain your code. The CASE statement is in this regard, a lot better than a DECODE statement.

              Looking just at performance (which I suggest you do not do in isolation!!), I get mixed results using 10G Enterprise on different platforms (HP-UX vs SUN AMD) and operating systems (HP-UX vs Linux).

              On the former the DECODE is slightly faster. On the latter the CASE is slightly faster.

              Using the construct you've specified and doing a tight loop of a 100,000 iterations, the elapsed execution times are:
              HP-UX DECODE = 00:00:11.83
              HP-UX CASE = 00:00:12.32
              Linux/AMD DECODE =00:00:02.02
              Linux/AMD CASE = 00:00:01.84

              Obviosuly the CPU architecture plays a major role here. The AMD is considered as the best 64 CPU on the market. The HP-UX PARISC CPU (also 64bit), does not really compare raw performance wise. In addition this is a RISC CPU whereas the AMD CPU is I believe more CISC than RISC.

              Also interesting that the faster Sun AMD/Linux server I used for this benchmark is about 10% the price of the HP-UX server.. and about 5x faster ito raw speed as this benchmark showed. :-)

              An interesting exercise, but one with little real world value. Yes performance is important. But within PL/SQL, not to the level about debating whether a CASE or DECODE is faster. As I've mentioned, I believe the right question being one about readability and maintenance and not performance in this case.


              Hope this helps and all credit goes to the expert billy.

              Edited by: Kevin CK on Aug 5, 2010 4:07 PM

              Edited by: Kevin CK on Aug 5, 2010 4:08 PM