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: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- To: 'Greg Rahn' <greg@xxxxxxxxxxxxxxxxxx>, "'Wolfgang Breitling (breitliw@xxxxxxxxxxxxx)'" <breitliw@xxxxxxxxxxxxx>, "'mwf@xxxxxxxx'" <mwf@xxxxxxxx>, "'jeremy.schneider@xxxxxxxxxxxxxx'" <jeremy.schneider@xxxxxxxxxxxxxx>
- Date: Fri, 12 Aug 2011 13:32:41 -0500
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
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.
Other related posts: