Re: DBA_OBJECTS using Distinct Vs Group By

From: Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>
Date: Thu, 22 Jun 2006 10:51:24 -0700 (PDT)
Message-ID: <20060622175124.5262.qmail@web52806.mail.yahoo.com>

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

Received on Thu Jun 22 2006 - 12:51:24 CDT