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

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,

emp_name,
dpt_name​

FROM employee,

department
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,

emp_name,
dpt_name​

FROM employee e1,

department​

WHERE emp_dept = dpt_id

AND e1.emp_grade IN (SELECT grd_id

FROM grade
WHERE grd_min_salary < 200000
INTERSECT
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.

https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/