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

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)

>" 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."

Great input - I have never considered using a visual diff tool on 10053 traces. Very interesting.

>" 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..."
I didn't catch that DISTINCT difference. That portion of the query is screwed up besides being different. (I can honestly say I didn't any of the original query). I'll play around with that portion just to see what the optimizer does as well. (I've changed that portion of the SQL in the production version of the code since the original email last week)

Thanks again for your input on this (in case I come across as unappreciative).

Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-663-1673
Email: chris.taylor_at_ingrambarge.com
 
CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.

-----Original Message-----
From: Greg Rahn [mailto:greg_at_structureddata.org] Sent: Friday, August 12, 2011 6:38 PM
To: Taylor, Chris David
Cc: Jeremy Schneider; oracle-l_at_freelists.org Subject: 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??")

Regardless of what is or is not happening with stats, the plans looks like they are getting poor cardinality estimates so that warrants further investigation.

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...

--
Regards,
Greg Rahn
http://structureddata.org


--
http://www.freelists.org/webpage/oracle-l