If you want to find the minimum salary among all employees, you may issue a SQL statement like this:
select min(emp_salary)
from employee
Plan
3 SELECT STATEMENT
2 SORT AGGREGATE
1 INDEX FULL SCAN (MIN/MAX) SQLEXP.EMP_SALARY_IDX
This SQL statement used “INDEX FULL SCAN (MIN/MAX) SQLEXP.EMP_SALARY_IDX” with good performance.
But, sometimes you may want to know maximum salary and number of employees within one SQL statement:
select min(emp_salary),
max(emp_salary),
count(*)
from employee
Plan
3 SELECT STATEMENT
2 SORT AGGREGATE
1 TABLE ACCESS FULL SQLEXP.EMPLOYEE
The performance of this SQL is bad, Oracle cannot resolve multiple aggregation functions with the best index search for each aggregation, I tested the same SQL in MS SQL Server, the result is a little bit better with a index scan on Employee table, but it still cannot divide multiple aggregation functions into individual aggregation to best utilize indexes.
Plan
6 SELECT
5 Compute Scalar
4 Stream Aggregate / Aggregate
3 Parallelism / Gather Streams
2 Stream Aggregate / Aggregate
1 Index Scan [sqlexp].[dbo].[EMPLOYEE].[EMP_SALARY_IDX]
In order to resolve this problem both for Oracle and SQL Server, I am planning to add a SQL transformation rule to both rewrite engines. The transformed SQL syntax will be like this:
with
t1(a)
as (select min(emp_salary) from employee),
t2(b)
as (select max(emp_salary) from employee),
t3©
as (select count(*) from employee)
select a, b, c
from t1, t2, t3
I don’t want to explain why it is faster, but will let you guys to test it out in Oracle or SQL Server, see if it is running faster or not in your environment ?