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

So it is interesting to me then that Oracle would pick a better plan for 
UNION-ALL vs UNION in that case with the same information available to the 
optimizer at the time each query is executed.

In that regard then, UNION would seem to be a more expensive operation 
REGARDLESS of the additional SORT operation that must occur.

(Theory) Following that, IF UNION-ALL is always less expensive than UNION 
(before the additional SORT operation) then the WINDOW function applied 
(depending on overhead) will OFTEN come out cheaper than a UNION.


Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-663-1673
Email: chris.taylor@xxxxxxxxxxxxxxx<mailto:chris.taylor@xxxxxxxxxxxxxxx>

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.

From: Jeremy Schneider [mailto:jeremy.schneider@xxxxxxxxxxxxxx]
Sent: Friday, August 12, 2011 2:49 PM
To: Taylor, Chris David
Cc: Greg Rahn; Wolfgang Breitling (breitliw@xxxxxxxxxxxxx); mwf@xxxxxxxx; 
oracle-l@xxxxxxxxxxxxx
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??")

Aaaah, it's clear now.  This has nothing to do with an optimization between 
UNION vs ROW_NUMBER -- it's that your plan completely changed when you switched 
the syntax -- most importantly the table join order.  If you use the same join 
order with the UNION then you should see similar performance.

It seems that there are two table join orders with nearly identical cost.  
Something in the costing of the UNION statement causes the optimizer to pick 
the unlucky order, whereas with the UNION ALL it picks the more lucky one.

-Jeremy

On Fri, Aug 12, 2011 at 1:32 PM, Taylor, Chris David 
<ChrisDavid.Taylor@xxxxxxxxxxxxxxx<mailto:ChrisDavid.Taylor@xxxxxxxxxxxxxxx>> 
wrote:
First, disregard the part about the large TEMP tablespace usage.  I think that 
was coming from the query _before_  I rewrote it to use WITH statements.  I'm 
not seeing the large TEMP usage now.

For each number below, I've included the dbms_xplan information, the row source 
operations, and the timings.

#1 The original query using WITH statements and UNION
http://pastebin.com/embed_iframe.php?i=0Ht0V4T3
Time: 18 minutes, 38 secs (89,200 rows)

#2 The original query CHANGED from UNION to UNION ALL (only change):
http://pastebin.com/embed_iframe.php?i=JybML3y8
Time: 1 minute, 03 secs (90,227 rows)

#3 The query from #2 CHANGED to include the ROW_NUMBER function to give us the 
same results as #1:
http://pastebin.com/embed_iframe.php?i=75QJ2ShD
Time: 50 secs (89,200 rows)



Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355<tel:615-517-3355>
Cell: 615-663-1673<tel:615-663-1673>
Email: chris.taylor@xxxxxxxxxxxxxxx<mailto:chris.taylor@xxxxxxxxxxxxxxx>

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.




--
http://www.ardentperf.com
+1 312-725-9249

Jeremy Schneider
Chicago

Other related posts: