1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Speed up Aggregation Functions by Multiple Subqueries

Discussion in 'Oracle' started by Richard To, Jun 6, 2019.

  1. Richard To

    Richard To New Member

    Joined:
    Sep 18, 2017
    Messages:
    13
    Likes Received:
    1
    Trophy Points:
    3
    Gender:
    Male
    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
     
  2. Richard To

    Richard To New Member

    Joined:
    Sep 18, 2017
    Messages:
    13
    Likes Received:
    1
    Trophy Points:
    3
    Gender:
    Male
    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)
     

Share This Page