Message-ID: <4E4060C6.2090300@ardentperf.com>
Date: Mon, 08 Aug 2011 17:18:46 -0500
From: Jeremy Schneider <jeremy.schneider@ardentperf.com>
User-Agent: Mozilla/5.0 (Windows NT 6.0; rv:5.0) Gecko/20110624 Thunderbird/5.0
MIME-Version: 1.0
To: mwf@rsiz.com
CC: oracle-l@freelists.org, ChrisDavid.Taylor@ingrambarge.com
Subject: Re: Wow - has anyone used ROW_NUMBER() to get around using a UNION statement when UNION ALL doesn't work??
References: <C5533BD628A9524496D63801704AE56D6A315EBA35@SPOBMEXC14.adprod.directory>	<60AAF908-AF26-4150-A894-E96164ECAD15@centrexcc.com>	<C5533BD628A9524496D63801704AE56D6A315EBA72@SPOBMEXC14.adprod.directory>	<65857EE5-361A-4BB4-B88C-644E3871FFF1@centrexcc.com>	<C5533BD628A9524496D63801704AE56D6A315EBA9F@SPOBMEXC14.adprod.directory>	<CAGXkmiu=qZzLQWmeZZO_N96EaoUg-QYwr5cf2xTty1+w2K6anQ@mail.gmail.com>	<38D36890-880B-4D0F-B582-2FD94734D54B@ingrambarge.com>	<DUB111-W8638692899EFAD9D7B2E33A13F0@phx.gbl>	<E5A9AB01-DF92-4472-A56E-8506626CC53C@ingrambarge.com> <CAGXkmithjZ7QNcogMGXUoR7qUq-TYXnimQcTr_UfwVA0M7-+3g@mail.gmail.com> <00b901cc55e8$0ef2ab60$2cd80220$@rsiz.com>
In-Reply-To: <00b901cc55e8$0ef2ab60$2cd80220$@rsiz.com>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: jeremy.schneider@ardentperf.com

On 8/8/2011 11:27 AM, Mark W. Farnham wrote:
> In the event that you know a subset of the columns of a UNION ALL projection
> such that ordering by the column subset guarantees that duplicates appear
> together, then you can generate that special case of UNION ALL to UNION
> de-duplication at a lower cost. Chris has related one such mechanism, using
> the ROW_NUMBER() analytical function. It may have been unclear that he was
> relying on a subset of the columns of the projection. If he needed all the
> columns for the grouping, his mechanism would be at best a tie (and I write
> that in the sense that the best you can do in any transfer of energy is have
> zero increase in entropy, except you really can only break even in the math
> and in reality you always lose.)

I think that in his original email, Chris said he included all the
columns in the "partition by" clause.


On 8/5/2011 1:35 PM, Taylor, Chris David wrote:
> 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).


Which is why there are so many questions about this. As Mark has pointed
out, if you're partitioning on all the columns then you're doing exactly
the same sort that's the UNION is doing. Something just isn't quite
right about the results; both methods should be doing nearly the
identical work.

Or to put it another way, if manually sorting all the records and
removing dups addes 0 seconds and 0 TEMP tablespace to the subquery
without the ROW_NUMBER() analytic clause, then either the sort is not
really happening (the fastest way to do anything) or there's something
wrong with the UNION which ought to be running in the same amount of
time and resources. At least this is how it seems to me, and might be
the reason there are so many questions.

-Jeremy

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

Jeremy Schneider
Chicago

--
http://www.freelists.org/webpage/oracle-l