How to Tune SQL with IN Subquery with Intersect for Oracle?

Discussion in 'Oracle' started by Richard To, Feb 15, 2023.

  1. Richard To

    Richard To Member

    Sep 18, 2017
    Likes Received:
    Trophy Points:
    Here is an example SQL that retrieves data from EMPLOYEE and DEPARTMENT table with the employee’s grade code in the GRADE table.

    SELECT emp_id,
    FROM employee,
    WHERE emp_dept = dpt_id
    AND emp_grade IN (SELECT grd_id
    FROM grade
    WHERE grd_min_salary < 200000)​
    and emp_dept<'D'

    Here the following is the query plan of this SQL, it takes 8.3 seconds to finish. The query plan shows a Hash Join with GRADE and EMPLOYEE and then hash join to DEPARTMENT. It looks like Oracle gave up any Nested Loops operations after the actual number of rows is returned from the GRADE table in this adaptive plan.
    In order to ask Oracle to consider the Nested Loops operations, I added an extra Intersect operation in the subquery to rapidly narrow down the result set of grd_id returned from the GRADE table first.

    SELECT emp_id,
    FROM employee e1,
    WHERE emp_dept = dpt_id
    AND e1.emp_grade IN (SELECT grd_id
    FROM grade
    WHERE grd_min_salary < 200000
    SELECT e1.emp_grade FROM employee e1
    WHERE emp_dept < 'D')​
    AND emp_dept < 'D'

    The rewritten SQL generates a query plan that is entirely different from the original query plan, The new plan is using “Nested Loops” from DEPARTMENT to EMPLOYEE as the first steps and then Hash Join to the GRADE table. The new plan now takes 0.81 seconds only.
    This kind of rewrite can be achieved by Tosska SQL Tuning Expert Pro for Oracle automatically, it shows that the rewrite is more than 10 times faster than the original SQL.

    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