How to Tune SQL Statement with “< All (subquery)” Operator for Oracle?

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

  1. Richard To

    Richard To Member

    Sep 18, 2017
    Likes Received:
    Trophy Points:
    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.
    shabbir likes this.

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice