Using DECODE to Reduce Processing

Using DECODE to Reduce Processing : 
The DECODE statement provides a way to avoid having to scan the same rows repetitively, or to join the same table repetitively. Consider the following example:

SELECT COUNT(*), SUM(salary)
FROM   emp
WHERE  dept_no  =  0020
AND    emp_name LIKE 'SMITH%' ;
SELECT COUNT(*), SUM(salary)
FROM   emp
WHERE  dept_no  =  0030
AND    emp_name LIKE 'SMITH%' ;
You can achieve the same result much more efficiently with DECODE:

SELECT COUNT(DECODE(dept_no, 0020, 'X',    NULL)) D0020_kount,
       COUNT(DECODE(dept_no, 0030, 'X',    NULL)) D0030_kount,
       SUM  (DECODE(dept_no, 0020, salary, NULL)) D0020_sal,
       SUM  (DECODE(dept_no, 0030, salary, NULL)) D0030_sal
FROM   emp
WHERE  emp_name LIKE 'SMITH%';

Remember that null values are never included in, nor do they affect the outcome of, the COUNT and SUM functions.