DECODE函数的性能

文章转自：[url]http://dingchaoqun12.blog.163.com/blog/static/1160625042010111504655692/[/url]

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。例如:

SELECT COUNT(*), SUM(Sal) FROM Emp WHERE Dept_No = 0020 AND Ename LIKE 'SMITH%';
SELECT COUNT(*), SUM(Sal) FROM Emp WHERE Dept_No = 0030 AND Ename LIKE 'SMITH%';

你可以用DECODE函数高效地得到相同结果：

SELECT COUNT(DECODE(Dept_No, 0020, 'X', NULL)) D0020_COUNT,
COUNT(DECODE(Dept_No, 0030, 'X', NULL)) D0030_COUNT,
SUM(DECODE(Dept_No, 0020, SAL, NULL)) D0020_SAL,
SUM(DECODE(Dept_No, 0030, SAL, NULL)) D0030_SAL
FROM Emp
WHERE Ename LIKE 'SMITH%';