Oracle Performance Optimization Operation 12: Merging multiple scans with case statements

We often have to calculate different aggregates based on multiple sets of data tables. For example, the following example passes three independent queries:

Select COUNT (*) from EMP where Sal<1000;select count (*) from EMP where Sal between, and 5000;select count (*) from E MP where sal>5000;

So we need to make three full table queries, but if we use case statements:

Select count (Sale when Sal <1000then 1 else null end)              count_poor,count (sale when between and 5000then 1 else Null end)              count_blue_collar,count (Sale when Sal >5000then 1 else null end)              count_poorfrom EMP;

The results of this query are the same, but the execution plan only makes one full table query.

Oracle Performance Optimization Operation 12: Merging multiple scans with case statements