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

improve oracle database performance

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.

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.