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