Originally posted by r937
probably slightly better? no, only possibly slightly better
probably no difference whatsoever
in any relational database worthy of the name, a true case of six-o'-one or half-dozen-o'-t'other
leave the optimization to the optimiser
I would have to disagree with you on that point. If the WHERE clause specified a field in the join it is likely that the optimiser would apply that filter before the outer join.
Typically, a WHERE clause *would* be applied after the join. I'm sure there are circumstances where the optimiser would also be able to use the table stats to rewrite the query, possibly where fields are indexed but I couldn't say for sure.
Bottom line, it's a bit naive to think that the optimiser will always do the work for you. Sometimes it helps if you can write decent SQL yourself!
Originally posted by cchattoraj
My understanding is that in an indexed situation, a nested select performs not as well since once the data is in the BP it looses it's indexed access property. Can someone clarify?
My understanding is that once materialized, a nested table expression is held in memory unless it is so large that it should be written to disk. Retrieval of the data held in memory should be incredibly quick as it involves no disk IO. You would still have the benefit of any indexes used within the nested table expression, so provided you don't expect a massive, materialized results set, I don't see it impacting on performance.