Re: UNION ALL with ROW_NUMBER vs UNION (WAS: "Wow - has anyone used ROW_NUMBER() to get around using a UNION statement when UNION ALL doesn't work??")

To say the only difference in the SQL is UNION vs UNION ALL is not the same as to say the UNION execution plan is identical UNION ALL execution plan other than the sort unique required (e.g. the input into the UNION or UNION ALL row source).

On Fri, Aug 12, 2011 at 5:33 PM, Taylor, Chris David <ChrisDavid.Taylor_at_ingrambarge.com> wrote:
> Thank you, that is some good information - something you say puzzles me however:
>
>> "My point is here is that generalization of UNION vs UNION ALL assumes all the rest of the plan is the same -- and in this case it is not..."
>
> Am I mistaken in thinking that the PLAN is dependent upon the SQL?   That is (if I can word this the way I want), isn't the plan created by the optimizer based on what's in the SQL?  So that the SQL contains a UNION the optimizer generates a PLAN, and when the SQL contains a UNION ALL in this case, it generates a different plan? (scenarios #1 & #2 only)

-- 
Regards,
Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l