Sunday, December 02, 2007

Combine Multiples Scans with CASE Statements

Combining multiple scans into one scan can be done by moving the WHERE condition of each scan into a CASE statement, which filters the data for the aggregation.



hr@ORCL> select count(*) from employees where salary < 10000;
COUNT(*)
----------
87

hr@ORCL> select sum(salary) from employees where salary < 10000;
SUM(SALARY)
-----------
463795.5

hr@ORCL> select avg(salary) from employees where salary < 10000;
AVG(SALARY)
-----------
5330.98276

hr@ORCL> SELECT COUNT (CASE WHEN salary < 10000
2 THEN 1 ELSE null END) count1,
3 SUM (CASE WHEN salary < 10000
4 THEN salary ELSE null END) sum1,
5 AVG (CASE WHEN salary < 10000
6 THEN salary ELSE null END) avg1
7 FROM employees;

COUNT1 SUM1 AVG1
---------- ---------- ----------
87 463795.5 5330.98276


No comments: