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

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