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

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_at_structureddata.org> 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_at_ingrambarge.com> 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

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