The reasons belong to the optimizer alone. Based on the 5 dozen different variables it looked at, it decided that out of the 5000 ways it could solve a query of that size, it *thought* the best way was actually *not* to wait until the very last step to do an intgersect. It re-wrote the intersect out of the query (which it always does) and simply came up with a different plan than you expected. All I can tell you is that the optimizer makes mistakes.

How do you stop it? Well, if you mean how do I force the intersect to be the last step when I do exactly this type of statement, the answer is easy, add AND ROWNUM > 0 to both sub-queries. This will force the optimizer to solve the queries independently, thus forcing the intersect to be done last. You could also use a lot of hints to do the same thing, and it would probably be 'more correct', but it would also be more difficult. The rownum thing is easier.

If you mean 'How do I stop the optimizer from making mistakes and/or doing things that I don't expect? Well, all I can say is can you please let the rest of us know when you figure that one out, eh?

Lesson of the day? Never assume you know what the optimizer is or *should* be doing. *Always* check the plan.

- Chris