UNION ALL of two very fast SELECT's generates very poor optimizer plan
Hi,
This is driving me nuts. I have a couple of moderately complex SQL statements which perform blindingly fast when run by themselves (< 2 seconds), but when joined together with a UNION ALL generate a really bad query plan that doesn't finish in 10 minutes. Oracle insists on a full table scan on the very largest table (10million rows) whereas when run outside the Union, it uses a high selectivity index.
These SQL statements are for different tables (one is for budgets, one is for actuals). I have tried rewriting the SQL statements, introducing hints (it gets even worse!!), using inline views, etc, nothing seems to help.
The datbase version is 9.2.0.5 and yes, the statistics are completely up to date. A search of the Oracle documentation and the usual places such as Dizwell & asktom haven't enlightened me as to what is going on. I understand that a UNION will sort the entire result set to eliminate duplicates, which is irrelevant to me as I won't be getting duplicates, so I use a UNION ALL which doesn't behave like this.
Can anyone explain to me what is going on, how can I persuade the optimizer to use the really really good plan it came up with a moment ago , instead of the really really bad one it comes up when using a union. Is this a known issue or is it just me and my particular SQL statement?
Thanks,
Steve