In 10gr2 oracle upgraded the sorting and hashing algorithums, maybe distinct
will be upgraded in 11.

-----Original Message-----
From: oracle-l-bounce@(protected)]
On Behalf Of Deepak Sharma
Sent: Thursday, June 22, 2006 12:51 PM
To: mcdonald.connor@(protected)
Subject: Re: DBA_OBJECTS using Distinct Vs Group By

That doesn't seem to be it. I tried disabling "HASH
GROUP BY" and the plan now shows "SORT GROUP BY", but
still the "GROUP BY" timing is same with or w/o this
setting.

--> "_gby_hash_aggregation_enabled"=false

The one difference I see in the plan for "DISTINCT" is
it does a Hash Join between and "I_OBJAUTH1" and
"X$KZSRO", whereas the "GROUP BY" plan shows a Nested
Loop between the same two. In fact the GROUP BY
query's cost is higher (but of course that doesn't
govern the timing necessarily).

Thanks,
Deepak Sharma

--- Connor McDonald <mcdonald.connor@(protected):

> On 6/22/06, Deepak Sharma
> <sharmakdeep_oracle@(protected):
> >
> > Any ideas why "GROUP BY" query is faster that
> > "DISTINCT" in this query?
> >
> > SELECT OWNER FROM DBA_OBJECTS GROUP BY OWNER;
> >
> >
> >
> Without doing any real analysis on it, I'm guessing
> that the group by is
> getting benefit from the new "HASH GROUP BY" in 10g.
>


__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
http://www.freelists.org/webpage/oracle-l


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