DECODE

if sal = 5000 then
salary := sal*3
end if;

if sal > 5000 then
salary := sal*2
end if;

if sal < 5000 then
salary := sal*4
end if;

The above IF condition I want to convert in DECODE the reason why I need it to be in DECODE, Because I am writing SQL report so I can not use IF condition in SQL. There are 3 diferent. formulas I have to use.

------------------------------
just for your ref.

SELECT
ename,
deptno,
hiredate,
DECODE(sal,5000,sal*3) salary
FROM emp;

How to check for > and < sal in DECODE or there is another way to handle this problem???
---------------------------------------

You can't use > or < in decode.

Use UNION operator, for example:

select emp, sal, sal*3
from emp where sal=5000
union
select emp, sal, sal*2
from emp where sal<5000
union
select emp, sal, sal*4
from emp where sal > 5000
;

Actually, you can play some math tricks to get your desired results using a DECODE.


    select ENAME, DEPT, HIREDATE,
           DECODE( SIGN( SAL - 5000 ), -1, SAL * 4, 0, SAL * 3, 1, SAL * 2 )
    from EMP;


HTH,

Heath

Of course, if you are using 8i, you can simply use:

SELECT
---ENAME,
---DEPTNO,
---HIREDATE,
---CASE
------WHEN---SAL = 5000---THEN
---------SAL*3
------WHEN---SAL > 5000---THEN
---------SAL*2
------WHEN---SAL < 5000---THEN
---------SAL*4
------ELSE
---------SAL
---END
------AS---SALARY
FROM EMP;

- Chris

Hey Chris,

Pretty sweet. I forgot about the CASE in Oracle. Is there any performance hit by using the CASE statement over the DECODE?

I think the CASE was added in 8i to support ansi standards.

Performance penalty?? Not in the least. CASE is actually quite a bit faster than DECODE. So it's cleaner, more powerful and faster. The only drawback is that you can't use it in PL/SQL. This is because Oracle, in its infinite wisdom, has maintained a separate SQL engine for straight SQL versus PL/SQL and they don't always implement the latest and greatest in the PL/SQL - SQL engine. Brilliant, eh ;)? At least they wised up and merged the two engines in 9i. For now, however, you must use dynamic SQL to implement the CASE statement or analytical functions in PL/SQL.

- Chris

Quote:

---

Originally posted by chrisrlong
Performance penalty?? Not in the least. CASE is actually quite a bit faster than DECODE.

---

Chris,

What makes you think CASE is considerably faster than DECODE? Have you perform any tests? Your statement made me qurious, so I performed some tests, which showed virtualy exactly the same performance between CASE and DECODE.

Since in 8.1.* we (still) can't use CASE inside the PL/SQL I executed looping iterations using NDS. Here is the code of both tests, they both showed virtualy equal timings (around 8 secs on my computer):

Code:

---

declare
  y number;
begin
  for i in 1 .. 10000 loop
    y := i;
    execute immediate
      'select CASE
              when :1 < 5000 then :1 * 4
              when :1 = 5000 THEN :1 * 3
              else :1 * 2
            end
        from dual'
    using y, y, y, y, y;
  end loop;
end;
/

declare
  y number;
begin
  for i in 1 .. 10000 loop
    y := i;
    execute immediate
      'select DECODE( SIGN(:1  - 5000 ),
                      -1, :1 * 4,
                      0, :1 * 3,
                      1, :1 * 2 )
      from dual'
    using y, y, y, y;
  end loop;
end;
/

---

Quote:

---

Originally posted by jmodic

Quote:

---

Originally posted by chrisrlong
Performance penalty?? Not in the least. CASE is actually quite a bit faster than DECODE.

---

Chris,

What makes you think CASE is considerably faster than DECODE? Have you perform any tests?

---

Ouch! You should have known you can't sneak a blanket statement past jmodic...

Never let me get away with anything, eh? ;)

Okay, here goes...

The problem is that the vast majority of time for your test is taken up by stuff other than the DECODE. If you ran your test with neither a DECODE nor a CASE, you would likely end up with the exact same time. In other words, you need to be able to measure a delta between a baseline of just selecting a column and actually using the DECODE *before* you can possibly measure the delta between the DECODE and the CASE.

A better test might be SELECTing all the records from a large table and comparing those results. Here are 2 such statements:

BEGIN
---DBMS_OUTPUT.PUT_LINE ( 'START - ' || TO_CHAR(SYSDATE,'HH:MI:SS') );
---EXECUTE IMMEDIATE 'DROP TABLE FRED';
---EXECUTE IMMEDIATE
------'
---------CREATE TABLE
------------FRED
---------AS
------------SELECT
---------------ORG_PK,
---------------CASE---WHEN---CRD_CNTCT_STATE_CD = ''AK''---THEN---1---ELSE 0 END AS TEST1,
---------------CASE---WHEN---CRD_CNTCT_STATE_CD = ''AZ''---THEN---2---ELSE 0 END AS TEST2,
---------------CASE---WHEN---CRD_CNTCT_STATE_CD = ''PA''---THEN---3 ---ELSE 0 END AS TEST3,
---------------CASE---WHEN---CRD_CNTCT_STATE_CD = ''MD''---THEN---4 ---ELSE 0 END AS TEST4,
---------------CASE---WHEN---CRD_CNTCT_STATE_CD = ''CA''---THEN---5 ---ELSE 0 END AS TEST5,
---------------CASE---WHEN---CRD_CNTCT_STATE_CD = ''NY''---THEN---6 ---ELSE 0 END AS TEST6,
---------------CASE---WHEN---CRD_CNTCT_STATE_CD = ''NJ''---THEN---7 ---ELSE 0 END AS TEST7,
---------------CASE---WHEN---CRD_CNTCT_STATE_CD = ''FL''---THEN---8 ---ELSE 0 END AS TEST8,
---------------CASE---WHEN---CRD_CNTCT_STATE_CD = ''GA''---THEN---9 ---ELSE 0 END AS TEST9,
---------------CASE---WHEN---CRD_CNTCT_STATE_CD = ''NC''---THEN---10 ---ELSE 0 END AS TEST10
------------FROM
---------------ORG, RGLTR
------'---;
---DBMS_OUTPUT.PUT_LINE ( 'STOP - ' || TO_CHAR(SYSDATE,'HH:MI:SS') );
END;


BEGIN
---DBMS_OUTPUT.PUT_LINE ( 'START - ' || TO_CHAR(SYSDATE,'HH:MI:SS') );
---EXECUTE IMMEDIATE 'DROP TABLE FRED';
---EXECUTE IMMEDIATE
------'
---------CREATE TABLE
------------FRED
---------AS
------------SELECT
---------------ORG_PK,
---------------DECODE(---CRD_CNTCT_STATE_CD,---''AK'', 1, 0---)---AS TEST1,
---------------DECODE( ---CRD_CNTCT_STATE_CD,---''AZ'', 2, 0---)---AS TEST2,
---------------DECODE(---CRD_CNTCT_STATE_CD,---''PA'', 3, 0---)---AS TEST3,
---------------DECODE( ---CRD_CNTCT_STATE_CD,---''MD'', 4, 0---)---AS TEST4,
---------------DECODE( ---CRD_CNTCT_STATE_CD,---''CA'', 5, 0---)---AS TEST5,
---------------DECODE( ---CRD_CNTCT_STATE_CD,---''NY'', 6, 0---)---AS TEST6,
---------------DECODE( ---CRD_CNTCT_STATE_CD,---''NJ'', 7, 0---)---AS TEST7,
---------------DECODE( ---CRD_CNTCT_STATE_CD,---''FL'', 8, 0---)---AS TEST8,
---------------DECODE( ---CRD_CNTCT_STATE_CD,---''GA'', 9, 0---)---AS TEST9,
---------------DECODE( ---CRD_CNTCT_STATE_CD,---''NC'',10, 0---)---AS TEST10
------------FROM
---------------ORG, RGLTR
------'---;
---DBMS_OUTPUT.PUT_LINE ( 'STOP - ' || TO_CHAR(SYSDATE,'HH:MI:SS') );
END;

The trick is then to pick a table with enough rows that you can notice the difference yet not more rows than fit in the cache. Otherwise, you will introduce I/O issues which will likely hide the CASE benefits.

The final requirement is to perform the test on a machine where you can actually see the difference - my servers here are too powerful. I can't even distinguish between a straight column select and a decode usage. I'll try to remember to run a test tonight at home, though. I should be able to see it there.

Anyway, per the Oracle Docs:
"CASE statements are similar in purpose to the Oracle DECODE statement, but they offer more flexibility and logical power. They are also easier to read than traditional DECODE statements, and offer better performance as well. "
...
"Because this query does not require a PL/SQL function invocation, it is much faster. "

The main difference would be that a context switch is not required for each row in the statement with a CASE. Now, as I said, it is not really noticable in powerful, multi-processor servers where I/O is the only true bottleneck. But, at the microsopic level of how long a DECODE actually takes, the CASE should be faster. I have definitely noticed a speed penalty with other context switches on my machine at home, so I would assume that I could measure this there.

Make sense?

- Chris

Quote:

---

Originally posted by chrisrlong
The trick is then to pick a table with enough rows that you can notice the difference yet not more rows than fit in the cache. Otherwise, you will introduce I/O issues which will likely hide the CASE benefits.

---

Actually, I disagree with your testing methodology. If you want to compare the raw speed of DECODE vs. CASE, it would actally be more consitent to NOT select data from the database. Introducing the database into the test brings inconsistent results because of the way the database may be tuned/sized.

Yeah, but we're not changing the plan and as long as you run it a few time, you should be fine. This is the situation where the differences would be most pronounced - inside a single SQL statement that either does or does not have to context switch a million times. In this case, staying within a single SQL statement is the best way to observe the differences.

- Chris

Chris, Jeff et all

In my initial test I followed the same logic as Jeff has mentioned - to avoid I/O operations (both physical and logical) as much as possible. Thet is why I've done trivial selects from DUAL inside the loop. But I've forgotten one thing that you have pointed out - context switches. Context switches caused by repeatedlly calling SQL select from inside PL/SQL block. BTW, your qoutes from the documentation imply that DECODE is a PL/SQL function while CASE is not (ie it is "internal" function), so that calling a DECODE causes an extra context switch compared to CASE, which is simply not true. They are both implemented the same way, as any other internal function.

In any case, I conducted my previous tests again at home, adding one additional test, where I used simple "SELECT NULL FROM DUAL;" inside the same loop to get the baseline results. It turned out that all the selects and context switches take roughly about 90% of time, while using either DECODE or CASE functions takes other 10% of the owerall time. The timing was performed in SQL*Plus using SET TIMING ON (1/100 secs precision) and was again allmost identical for both CASE and DECODE.

Then I performed another set of tests, according Chris's single-select proposal. In your example I didn't like DROP TABLE and CREATE TABLE AS SELECT stetements because both DROP and CREATE are very expensive operations for such tests and would probably distort the results. So what I realy did was create a simple one-column table and fill it with 500,000 rows that fitted in about 700 db blocks. Those blocks were all read into the cache so that there were no physical reads during the subsequent tests.

Then I performed three full table scans on that table: one with NULL in the column list, another one with CASE and the third one with DECODE.
- SELECT null FROM test_table;
- SELECT CASE ... FROM test_table;
- SELECT DECODE(...) FROM test_table;

I also noticed that mesuring time with SQL*Plus's TIME ON is rather unreliable, so I performed timing by using V$TIMER before and after every select. I performed several iteration of this tests and here are the average results in seconds elapsed (absolute numbers are not so important, I have rather slow PC):

- without any function: 19.22
- with CASE: 21.80
- with DECODE: 23.25

So we can see again that pure select uses roughly 80-85% of the total time, while the remaining 15-20% is spent on CASE/DECODE execution. (So the context switches in my first testing were not so very expensive, after all). We can also see that CASE is indeed quite a bit faster than DECODE, however absolutely unmesurable in a single execution. Performing 500.000 executions of either of them took 2.58 secs for CASE and 4.03 secs for DECODE.

So I would say performance is not realy a serious reason for switching from DECODE to CASE. But the elegance and mantainability of the code - well, this is certanly absolutely a different story....

Quote:

---

Originally posted by jmodic
BTW, your qoutes from the documentation imply that DECODE is a PL/SQL function while CASE is not (ie it is "internal" function), so that calling a DECODE causes an extra context switch compared to CASE, which is simply not true.

---

You are absolutely correct. I had completely mis-read what they were saying in that part of the docs. My bad! :)

Good tests, good results and good conclusions!!


Kudos

- Chris

(Told ya it was faster ;););))