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

Discussion in 'Oracle' started by Richard To, May 12, 2021.

  1. Richard To

    Richard To Member

    Joined:
    Sep 18, 2017
    Messages:
    59
    Likes Received:
    19
    Trophy Points:
    8
    Gender:
    Male
    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.
    [​IMG]
    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.
    [​IMG]
    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/
     

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