group by nosort

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 - 18:39:29 CDT