Speed up Aggregation Functions by Multiple Subqueries

A SQL statement with multiple aggregation functions like the following and the emps_salary and emps_id are indexed. If you find the SQL is slow and want to speed up the SQL

SELECT min(emps_salary), max(emps_id), count(*)
FROM employees

You can rewrite the SQL into the following syntax, the new syntax may better utilize Fast/Full Index Scan.

Try it and have fun….

WITH
t1 (Min_salary) as (select min(emps_salary) from employees),
t2 (Max_emps_id) as (select max(emps_id) from employees),
t3 (Count_all) as (select count(*) from employees)
SELECT Min_salary, Max_emps_id, Count_all
FROM t1, t2, t3

 

Another example:
select *
from employees
where (emps_salary, hire_date) in (select Max(emps_salary),
to_char(sysdate, 'DD-MM-YY')
from employees)


Can be transformed to:

with t1 (a) as (select Max(emp_salary)
from employees),
t2 (b) as (select to_char(sysdate,'DD-MM-YY')
from dual)
select *
from employees
where (emps_salary, emps_grade) in (select a,
b
from t1,t2)