Oracle performance optimization operation 12: Use Case statements to merge multiple scans, oraclecase

Oracle performance optimization operation 12: Use Case statements to merge multiple scans, oraclecase

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

Select count (*) from emp where sal <1000; select count (*) from emp where sal between 1000 and 5000; select count (*) from emp where sal> 5000;

In this way, we need to perform three full table queries, but if we use the case statement:

Select count (sale when sal <1000 then 1 else null end) count_poor, count (sale when between 1000 and 5000 then 1 else null end) count_blue_collar, count (sale when sal> 5000 then 1 else null end) count_poorfrom emp;

In this way, the query results are the same, but the execution plan only performs a full table query once.