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:32:49 -0700

Part 2:

Now can you tell why #3 is much faster?

Two hints:
1) it has nothing directly to do with the UNION vs window function, etc.
2) see http://pastebin.com/AUicQgiX

On Fri, Aug 12, 2011 at 12:18 PM, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote:

> 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

References:

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

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

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

Other related posts: