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

How to Tune SQL Statement with multiple MAX() functions for Oracle?

Discussion in 'Oracle' started by Richard To, Oct 17, 2020.

  1. Richard To

    Richard To Member

    Joined:
    Sep 18, 2017
    Messages:
    31
    Likes Received:
    14
    Trophy Points:
    8
    Gender:
    Male
    Here the following is a very simple SQL statement with two Max() functions in select list.

    select max(emp_salary),max(emp_id)
    from employee;

    Here the following are the query plans of this SQL, it takes 8.82 seconds to finish. The query shows a Full Table Scan of Employee table.
    [​IMG]
    You can see that this SQL cannot utilize index scan even though the emp_id and emp_salary are indexed.
    If I change the SQL to select max(emp_salary) only like the following:

    select max(emp_salary) from employee;

    The SQL will run much faster and the emp_salary index will be used.

    In order to solve this problem, let me rewrite the SQL into the following syntax. I use two WITH (common table expression) to select each max() function independently and it fully utilize the index in each column.

    WITH
    dt1
    AS (SELECT Max(emp_salary)
    FROM employee),
    dt2
    AS (SELECT Max(emp_id)
    FROM employee)​
    SELECT *
    FROM dt1, dt2;

    Here is the query plan of the rewritten SQL and the speed is 0.00 seconds which cannot be detected in our timing scale. The new query plan shows that two Index Full Scan (MIN/MAX) for each column are used now.
    [​IMG]
    This kind of rewrite can be achieved by Tosska SQL Tuning Expert for Oracle automatically, it shows that the rewrite is much faster than the original SQL.

    https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/
    [​IMG]
     
    shabbir likes this.

Share This Page