Here the following is SQL statement with multiple OR subqueries. 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/
