Re: Sorting update?

From: Mike Burden <mburden_at_uk.att.com>
Date: Sun, 26 Sep 1999 00:00:16 +0100
Message-ID: <37ED5400.FE5FB0F0@uk.att.com>

For all those who wish to know the salary is needed to prevent a second sort. When using the in clause the optimizer removes distinct values (for efficiency) and undoes the good work of the group by sort. Although, why it can't work out that rowid is unique is another question which only Oracle can answer.

A semi product join is a name I made up. Basically, the example query was proportional to N*N/2 where N is the number of rows. The 1/2 gave me the word semi. Just added it for fun. Sad really.

A
rtproffitt_at_my-deja.com wrote:

> I like the simplicity of Thomas' and Mike's queries....
>
> As for Mike's question...
> The salary in the where ..in clause is necessary.
> I think it is because Rownum is coming from the
> outer table EMP T1. Without Salary, the rownums are
> wrong.... I think that specifying rownum,salary
> forces some kind of sort or index use on the outer T1
> table in order to get it to match with the
> inner EMP table...perhaps so that the IN clause
> could be more efficient....
>
> What's a semi-product join.
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Sat Sep 25 1999 - 18:00:16 CDT