Here the following is a simple SQL statement with a “< ANY (Subquery)” syntax.
SELECT *
FROM employee
WHERE emp_salary< ANY (SELECT emp_salary
FROM emp_subsidiary
where emp_dept=‘AAA’
)
Here the following is the query plan of the SQL, it takes 18.49 seconds to finish. The query shows a “TABLE ACCESS FULL” of EMPLOYEE table and “MERGE JOIN SEMI” to a VIEW that is composed of a HASH JOIN of two indexes “INDEX RANGE SCAN” of EMP_SUBSIDIARY.
You can see that it is not an efficient query plan if we know that the emp_salary of EMP_SUBSIDIARY is a not null column, we can rewrite the SQL into the following syntax. The Nvl(Max(emp_salary),-99E124)is going to handle the case that if the subquery returns no record, the -99E124 representing the minimum number that the emp_salary can store to force an unconditional true for the subquery comparison.
SELECT *
FROM employee
WHERE emp_salary < (SELECT Nvl(Max(emp_salary),-99E124)
FROM emp_subsidiary
WHERE emp_dept = ‘AAA’)
Here is the query plan of the rewritten SQL and the speed is 0.01 seconds which is 1800 times better than the original syntax. The new query plan shows an “INDEX RANGE SCAN” instead of “TABLE ACCESS FULL” of EMPLOYEE.
This kind of rewrite can be achieved by Tosska SQL Tuning Expert for Oracle automatically, there are other rewrites with similar performance, but it is not suitable to discuss in this short article, maybe I can discuss later in my blog.
https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/