DECODE函数的性能 博客分类: database decode
程序员文章站
2024-03-22 20:13:34
...
文章转自:http://dingchaoqun12.blog.163.com/blog/static/1160625042010111504655692/
使用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%';
使用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%';