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

Ok a few items I've found.

1. PeopleSoft is reanalyzing some of the objects in question: http://pastebin.com/embed_iframe.php?i=1iS2y6TK

SYSADM	PS_PO_LINE_DISTRIB	TABLE	834268	         1	50549	08/12/2011 12:18:16 AM
SYSADM	PS_PO_LINE_DISTRIB	INDEX	866303	1	151379	08/12/2011 12:18:18 AM

2. I will rerun the test scenarios with updated 100% stats on all tables/indexes in the few days and report back.

3. It is still interesting that Oracle behaved differently with a UNION vs UNION ALL when operating off the same statistics information (scenario #1 vs scenario #2 in my original email).

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: Taylor, Chris David
Sent: Friday, August 12, 2011 4:58 PM
To: greg_at_structureddata.org
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??")

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.  

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