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??")
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- Date: Fri, 12 Aug 2011 17:56:48 -0700
Let me try to convey this a slightly different way:
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@xxxxxxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
