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

  • From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 5 Aug 2011 13:35:22 -0500

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<mailto: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.

Other related posts: