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: Jeremy Schneider <jeremy.schneider_at_ardentperf.com>
Date: Fri, 12 Aug 2011 15:38:01 -0500
Message-ID: <CA+fnDAbQNNFAZ0mR80fS+8bVWbSvgoUoyY9nmuRpuoXgo4oMyw_at_mail.gmail.com>



I don't know exactly how oracle would cost a UNION versus how oracle would cost a UNION-ALL plus a WINDOW. But it probably depends a bit on some environmental factors including row-source cardinality, work area size, estimated cardinality of sort columns, etc... But it seems immaterial which comes out cheaper from the CBO - you just have to decide which way to code it then help the optimizer get the best plan with your code. Personally I'd go with the union and fix the optimizing of that plan, which is the simpler and more readable to do the same thing. Then, a little digging to see where Oracle went wrong in it's guessing. Calling display_plan(null,null,'ALLSTATS LAST') right after an execution can help with this since it'll give you a side-by-side comparison of estimated to actual rowcounts. Ideally the optimizer should be getting the same plan for both ways of writing the query.

-J

On Fri, Aug 12, 2011 at 3:21 PM, Taylor, Chris David < ChrisDavid.Taylor_at_ingrambarge.com> wrote:

> So it is interesting to me then that Oracle would pick a better plan for
> UNION-ALL vs UNION in that case with the same information available to the
> optimizer at the time each query is executed.****
>
> ** **
>
> In that regard then, UNION would seem to be a more expensive operation
> REGARDLESS of the additional SORT operation that must occur.****
>
> ** **
>
> (Theory) Following that, IF UNION-ALL is always less expensive than UNION
> (before the additional SORT operation) then the WINDOW function applied
> (depending on overhead) will OFTEN come out cheaper than a UNION. ****
>
> ** **
>
> ** **
>
> *Chris Taylor*****
>
> *Sr. Oracle DBA*****
>
> Ingram Barge Company****
>
> Nashville, TN 37205****
>
> Office: 615-517-3355****
>
> Cell: 615-663-1673****
>
> Email: chris.taylor_at_ingrambarge.com****
>
> ****
>
> *CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential
> and may also be privileged. If you are not the named recipient, please
> notify the sender immediately and delete the contents of this message
> without disclosing the contents to anyone, using them for any purpose, or
> storing or copying the information on any medium.*****
>
> ** **
>
> *From:* Jeremy Schneider [mailto:jeremy.schneider_at_ardentperf.com]
> *Sent:* Friday, August 12, 2011 2:49 PM
> *To:* Taylor, Chris David
> *Cc:* Greg Rahn; Wolfgang Breitling (breitliw_at_centrexcc.com); mwf_at_rsiz.com;
> oracle-l_at_freelists.org
> *Subject:* 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??")****
>
> ** **
>
> Aaaah, it's clear now. This has nothing to do with an optimization between
> UNION vs ROW_NUMBER -- it's that your plan completely changed when you
> switched the syntax -- most importantly the table join order. If you use
> the same join order with the UNION then you should see similar performance.
>
> It seems that there are two table join orders with nearly identical cost.
> Something in the costing of the UNION statement causes the optimizer to pick
> the unlucky order, whereas with the UNION ALL it picks the more lucky one.
>
> -Jeremy
>
> ****
>
> On Fri, Aug 12, 2011 at 1:32 PM, 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)****
>
> ****
>
> ****
>
> ****
>
> *Chris Taylor*****
>
> *Sr. Oracle DBA*****
>
> Ingram Barge Company****
>
> Nashville, TN 37205****
>
> Office: 615-517-3355****
>
> Cell: 615-663-1673****
>
> Email: chris.taylor_at_ingrambarge.com****
>
> ****
>
> *CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential
> and may also be privileged. If you are not the named recipient, please
> notify the sender immediately and delete the contents of this message
> without disclosing the contents to anyone, using them for any purpose, or
> storing or copying the information on any medium.*****
>
> ****
>
>
>
>
> --
> http://www.ardentperf.com
> +1 312-725-9249
>
> Jeremy Schneider
> Chicago****
>

-- 
http://www.ardentperf.com
+1 312-725-9249

Jeremy Schneider
Chicago

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Aug 12 2011 - 15:38:01 CDT

Original text of this message