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??")

WRT the UNION vs UNION ALL, sure, the stats, etc. are a constant input to the CBO but what is happening is the CBO in the UNION ALL is unnesting both uncorrelated subqueries in both braches (VW_NSO_1 & VW_NSO_2), but the UNION only in the second branch (VW_NSO_1) and not the first. Why that is happening is unknown -- maybe a bug, maybe costing, maybe edge condition. 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 - for reasons not yet determined. At a high level, you are right, but the devil is in the details...

As a matter of debug & triage, you can get the full outline directive for both by using dbms_xplan.display_cursor('<sql_id>', null, 'outline') and find the hints that do the unnesting and try and force it in the UNION version. That won't give you the answer of why it's happening, but it's a good exercise in debugging plans. Ultimately the 10053 traces are probably needed to determine. If you want, grab the 10053 trace from both load them into a visual diff tool and see if you can track down a difference.

(took a break and thought more about this)

After looking at both branches there is an every so slight difference in those subqueries: the first branch has a DISTINCT which may be causing the issue (bug). There is no need to to have a DISTINCT in the first as IN either matches or not - it doesn't match multiple times (1 row on the left for 1 or more rows on the right). So try this - remove the DISTINCT and see if the UNION plan unnests both subqueries.

PL.ACTIVITY_ID IN (SELECT DISTINCT ING_VESSEL_ID... versus
DL2.ACTIVITY_ID IN (SELECT ING_VESSEL_ID... On Fri, Aug 12, 2011 at 2:57 PM, Taylor, Chris David <ChrisDavid.Taylor_at_ingrambarge.com> wrote:
> 10.2.0.4
>
> I collect stats every weekend using 100% sample size BUT given that this a Peoplesoft system, it is likely there are other stat commands being executed as part of App Engine programs.
>
> I will get the stats command this evening.
>
> I disagree with you on the generalization of UNION vs UNION ALL because BOTH operations are operating with the SAME information available to the optimizer (comparing #1 and #2).
>
> Sure, the row sources are different at different points precisely because Oracle did something different with the UNION prior to the final SORT UNIQUE.
>
> I believe I have a very solid stats strategy, though of course that may be my own pride :)
>
> Peoplesoft processing that includes stat gathering operations seldom cause problems but sometimes it does.
>

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