Here the following is SQL statement with multiple OR subquery.
SELECT *
FROM employee
WHERE emp_id IN (SELECT emp_id FROM emp_subsidiary where emp_dept = ‘ACC’)
OR emp_id IN (SELECT emp_id FROM employee where emp_dept = ‘COM’)
OR emp_id = 600000
Here the following are the query plans of this SQL, it takes 29 seconds to finish. The query plan shows that the OR conditions are partially converted to Union All statement, the “OR emp_id = 600000” condition is not converted to Union All operation, so three is a full table access on Employee in the query plan is found and most of the time is spent on this step.
Let me rewrite the OR conditions in to a subquery with UNION ALL operations in the following.
SELECT *
FROM employee
WHERE emp_id IN (SELECT emp_id
FROM (SELECT emp_id
FROM emp_subsidiary
WHERE emp_dept = ‘ACC’
UNION ALL
SELECT emp_id
FROM employee
WHERE emp_dept = ‘COM’
UNION ALL
SELECT 600000
FROM dual) dt1)
Here is the query plan of the rewritten SQL and the speed is 0.06 seconds. It is 480 times better than the original syntax. The extra “SELECT emp_id” from the “UNION ALL” subquery in green color is used to force the subquery have to be processed in a whole without merging into the main query.
This kind of rewrite can be achieved by Tosska SQL Tuning Expert for Oracle automatically, there are other rewrite with even better performance, but it is not suitable to discuss in the short article, maybe I can discuss later in my blog.
https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/
