How to Tune SQL Statement with Multiple OR Subqueries for Oracle?

Discussion in 'Oracle' started by Richard To, Nov 16, 2020.

  1. Richard To

    Richard To Member

    Sep 18, 2017
    Likes Received:
    Trophy Points:
    Here the following is SQL statement with multiple OR subqueries.

    SELECT *
    FROM employee
    WHERE emp_id IN (SELECT emp_id FROM emp_subsidiary where emp_dept = 'ACC')
    OR emp_id IN (SELECT emp_id FROM employee where emp_dept = 'COM')
    OR emp_id = 600000​

    Here the following are the query plans of this SQL, it takes 29 seconds to finish. The query plan shows that the OR conditions are partially converted to Union All statement, the “OR emp_id = 600000” condition is not converted to Union All operation, so three is a full table access on Employee in the query plan is found and most of the time is spent on this step.[​IMG]
    Let me rewrite the OR conditions in to a subquery with UNION ALL operations in the following.

    SELECT *
    FROM employee
    WHERE emp_id IN (SELECT emp_id
    FROM (SELECT emp_id
    FROM emp_subsidiary
    WHERE emp_dept = 'ACC'
    SELECT emp_id
    FROM employee
    WHERE emp_dept = 'COM'
    SELECT 600000
    FROM dual) dt1)​

    Here is the query plan of the rewritten SQL and the speed is 0.06 seconds. It is 480 times better than the original syntax. The extra “SELECT emp_id” from the “UNION ALL” subquery in green color is used to force the subquery have to be processed in a whole without merging into the main query.
    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.

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