1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

    Joined:
    Sep 18, 2017
    Messages:
    39
    Likes Received:
    17
    Trophy Points:
    8
    Gender:
    Male
    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'
    UNION ALL
    SELECT emp_id
    FROM employee
    WHERE emp_dept = 'COM'
    UNION ALL
    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.
    [​IMG]
    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/
    [​IMG]
     

Share This Page