Re: Wow - has anyone used ROW_NUMBER() to get around using a UNION statement when UNION ALL doesn't work??

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Fri, 5 Aug 2011 12:52:20 -0600
Message-Id: <60AAF908-AF26-4150-A894-E96164ECAD15_at_centrexcc.com>



What Oracle version?
Is the UNION using a sort or a "HASH UNIQUE"?

On 2011-08-05, at 12:35 PM, Taylor, Chris David wrote:

> I’ve got a query that uses a UNION statement to join 2 large data sets.
>
> When I run the statement it takes 20 minutes to execute (using UNION), returns 89,887 rows and consumes 5GB of TEMP tablespace.
>
> Now, I changed it to a UNION ALL statement to see what I have and I get 89,963 rows (too many), 2 minutes to execute and 0 TEMP tablespace. (I know there’s some caching going on here as well but we’ll ignore that for the moment.)
>
> Now, I put the query containing the UNION ALL in an INNER select, and I select all columns from it PLUS the ROW_NUMBER() function partitioning by all the columns and applying an order by to the function and call this column “ROW_KEY” (not very original I know).
>
> Finally, I wrap that query into another subselect and this type I append a where clause to the outside “WHERE ROW_KEY = 1”.
>
> Now, I get my correct 89,887 rows in right at 2 minutes and 0 TEMP tablespace (again some caching here I’m sure).
>
> I’m curious if anyone has tried this before, or if it is of interest to anyone.
>
> I have a pretty good test case we could play with if anyone is interested? (I also have plenty of trace files etc)
>
>
> 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.freelists.org/webpage/oracle-l
Received on Fri Aug 05 2011 - 13:52:20 CDT

Original text of this message