Thank you for your responses. I looked at the execution plan. I would have expected the combined query to largely inherit original subquery plans and combine them using UNIQUE SORT, etc. Instead, the first subquery subplan is completely rewritten (the second subquery subplan is identical).
From looking at the new plan I could understand why it runs slow - but why Oracle decides to behave so differently with effectively identical SQL code? What causes it and how could I prevent it?
The plans for independent subqueries and a combined query are attached. I realize that without seeing the original SQL they are not great use, nevertheless they illustrate the point. In a big combined query the optimizer suddenly decides to go for a very simple recursive condition check on several large tables.
Reply With Quote