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.
https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/
