Toad World Blog

How to speed up multiple aggregation functions apply to the same table

Jun 11, 2015 12:27:02 AM by Richard To

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(c)
    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 ?  

Tags: Oracle SQL Optimizer SQL Optimizer for Oracle

Richard To

Written by Richard To

Richard specializes in using AI technique to solve database performance problems.