Re: group by nosort

Hi Kirill,

This is a nice one!
First of all, you could question, why you are joining the table to itself in the second table. From the first example and from the relation point of view you don't seem to need it.
Secondly, you just demonstrated the parser is unable to detect (in the same second example) this is one and the same table you are aliasing. Personally, I would consider this bad news, to copy immediately to my archives (and keep in mind permanently).
The only other thing is an index hint. However, this index hint must include an alias, it won't probably help you.
Maybe we should ask Thomas Kyte and/or Pete Sharman to shed some light on this!

Hth,

Sybrand Bakker, Oracle DBA

Kirill Richine <krichine_at_radss.com> wrote in message news:3783e1fd.91440584_at_news.cadvision.com...
> Suppose I have a huge table T (c1, c2, c3)
> and there is a unique index on it T_PK (c1, c2)
>
> When I go
> select c1, c2, count (*)
> from t
> group by c1, c2
> the execution plan is something like this:
>
> SORT (GROUP BY NOSORT)
> INDEX (FULL SCAN) OF T_PK (UNIQUE)
>
> If, however, I go
> select a.c1, a.c2, count (*)
> from t a, t b
> where a.c1 = b.c1 and a.c2 = b.c2
> group by a.c1, a.c2
>
> the execution plan goes haywire:
> SORT (GROUP BY NOSORT)
> MERGE JOIN
> INDEX (FULL SCAN) OF T_PK (UNIQUE)
> SORT (JOIN)
> TABLE ACCESS (FULL) OF T
>
> Why is it doing this?
> The version is 8.0.4, the table is fully analyzed and optimizer mode
> is set to choose. Putting first_rows hint does not take any effect.
>
> If I supply a /*+ RULE */ hint, it produces:
> SELECT STATEMENT Optimizer=HINT: RULE
> SORT (GROUP BY)
> NESTED LOOPS
> TABLE ACCESS (FULL) OF T
> INDEX (UNIQUE SCAN) OF T_PK (UNIQUE)
>
> This is a little better but still what I am trying to achieve is a
> NOSORT and
>
> INDEX (FULL SCAN) OF T_PK (UNIQUE)
>
> instead of
>
> TABLE ACCESS (FULL) OF T
>
> Is it possible?
> It should be smart enough to see that the only thing it needs to
> access here is the pk index, which means it does not need to sort in
> the end. Am I expecting too much?
>
> Thanks.
> k.
Received on Wed Jul 07 1999 - 23:55:50 CDT