Here the following is a simple SQL statement with a “< ALL (Subquery)” syntax. SELECT * FROM employee a WHERE a.emp_salary <ALL(SELECT emp_salary FROM emp_subsidiary); Here the following are the query plans of this SQL, it takes 22.65 seconds to finish. The query shows a Full Table Scan of EMPLOYEE table and “MERGE JOIN ANTI SNA” to EMP_SUBSIDIARY by an “INDEX FAST FULL SCAN”. 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(Min(emp_salary),99E124) is going to handle the case that if the subquery return no record, the 99E124 representing the maximum number that the emp_salary can store to force an unconditional true for the subquery comparison. SELECT * FROM employee a WHERE a.emp_salary < (SELECT Nvl(Min(emp_salary),99E124) FROM emp_subsidiary); Here is the query plan of the rewritten SQL and the speed is 0.16 seconds which is 141 time better than the original syntax. The new query plan shows an INDEX FULL SCAN (MIN/MAX) emp_salary for EMP_SUBSIDIARY. This kind of rewrite can be achieved by Tosska SQL Tuning Expert for Oracle automatically, there are other rewrite with even better performance, but it is not suitable to discuss in the short article, maybe I can discuss later in my blog. https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/
