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

From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
To: ChrisDavid.Taylor@xxxxxxxxxxxxxxx
Date: Fri, 5 Aug 2011 12:52:20 -0600

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@xxxxxxxxxxxxxxx
>  
> 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.
>

Follow-Ups:
- RE: Wow - has anyone used ROW_NUMBER() to get around using a UNION statement when UNION ALL doesn't work??
  - From: Taylor, Chris David
- RE: Wow - has anyone used ROW_NUMBER() to get around using a UNION statement when UNION ALL doesn't work??
  - From: Taylor, Chris David

References:
- Wow - has anyone used ROW_NUMBER() to get around using a UNION statement when UNION ALL doesn't work??
  - From: Taylor, Chris David

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

Other related posts: