Die Oracle Function DECODE, oft zuwenig bekannt, ist eine sehr effiziente Verwendung eines IF-THEN-ELSE. Oft können grosse Performancegewinne erzielt werden durch DECODE wie das folgende Beispiel zeigt.

DECODE as a very effizient use of IF-THEN-ELSE

Use: DECODE(expr,search,result,default)
     if expr IS equal to search, Oracle returns result,
     if no match is found, Oracle returns default.
--
-- Quite slow is ...
--
SELECT COUNT(*), SUM(sal)
  FROM emp
WHERE deptno = 10
   AND ename LIKE 'SMITH%';
--
SELECT COUNT(*), SUM(sal)
  FROM emp
WHERE deptno = 30
   AND ename LIKE 'SMITH%';
--
-- ... the same result much more efficiently with DECODE
--
-- Remeber that NULL values are never included in,
-- nor do they affect the
-- outcome of, the COUNT and SUM functions
--
SELECT COUNT(DECODE(deptno,10,'*',NULL)) D10_count,
       COUNT(DECODE(deptno,30,'*',NULL)) D30_count,
         SUM(DECODE(deptno,10,sal,NULL)) D10_sal,
         SUM(DECODE(deptno,30,sal,NULL)) D30_sal
  FROM emp
WHERE ename like 'SMITH%';