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??")
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- Date: Fri, 12 Aug 2011 12:18:02 -0700
Let's use this as a training exercise...
Why is #1 so horrible in performance?
Hint: look at this: http://pastebin.com/frUSVFKA
On Fri, Aug 12, 2011 at 11:32 AM, Taylor, Chris David <
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)****
>
>
> **
>
--
Regards,
Greg Rahn
http://structureddata.org
Other related posts:
