RE: DBA_OBJECTS using Distinct Vs Group By

From: Ken Naim <kennaim_at_gmail.com>
Date: Thu, 22 Jun 2006 13:20:42 -0500
Message-ID: <02a601c69628$92ead6c0$b4ae6a44@KenHome>

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

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Deepak Sharma
Sent: Thursday, June 22, 2006 12:51 PM
To: mcdonald.connor_at_gmail.com; oracle-l_at_freelists.org 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_at_gmail.com> wrote:

> On 6/22/06, Deepak Sharma
> <sharmakdeep_oracle_at_yahoo.com> wrote:
> >
> > 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

Received on Thu Jun 22 2006 - 13:20:42 CDT