How to Tune SQL with Exists Operator in Certain Environment for Oracle?

Discussion in 'Oracle' started by Richard To, Dec 18, 2020.

  1. Richard To

    Richard To Member

    Sep 18, 2017
    Likes Received:
    Trophy Points:
    Here is an example SQL that retrieves data from EMPLOYEE table with “emp_id < 710000” and employee’s department code exists in DEPARTMENT table.

    select *
    from employee
    where emp_id < 710000
    and exists (select 'x'
    from department
    where dpt_id = emp_dept)​

    Here the following are the query plan of this SQL, it takes 34.22 seconds to finish. The query plan is very complicated, although the SQL is quite simple. It is not abnormal that Oracle uses a complex solution to solve simple data retrieval. This kind of complex plan steps is suitable for certain environments, but not for a simple database like this. I call it over-optimized query plan, which is due to the under estimated cost of this query plan. For complex plan like this, the cost estimation error is easily be amplified from step to step within the chain of plan steps.
    In order to ask Oracle to consider other query plans, I rewrite the EXISTS to IN with a new “group by dpt_id” operation that force Oracle SQL optimizer to execute the subquery first.

    SELECT *
    FROM employee
    WHERE emp_id < 710000
    AND emp_dept IN (SELECT dpt_id
    FROM department
    GROUP BY dpt_id)​

    The rewritten SQL generates a simpler query plan and it is actually running faster with 5.59 seconds only.
    This kind of rewrites can be achieved by Tosska SQL Tuning Expert for Oracle automatically, it shows that the rewrite is more than 6 times faster than the original SQL. There is a SQL rewrite with even better performance, it is a little bit complicated to discuss in this short article here. May be we can discuss later.

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