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. 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. 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/
