Hi,

I'm working on Oracle 9.2.0.4.

I noticed recently that Oracle makes a difference if a count() or a count(distinct) is executed against a column defined as unique (unique index + unique constraint + not null).

From my point of view a count() and count(distinct) against a unique column should take the same query time because Oracle should know that each column is unique.
However, as you can see below, when the distinct is used Oracle makes a group by and doesn't just aggregate the data.

So my question is: has anybody a explanation for that behavior or any idea what I can do so that Oracle is as fast with as without the distinct?

Code:
SQL>  select count(  id) from sales3;

 COUNT(ID)
----------
   2032542

Abgelaufen: 00:00:03.00

Ausführungsplan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'I_RN' (UNIQUE) (Cost=3 Card=1
          034313)

SQL> select count( distinct id) from sales3;

COUNT(DISTINCTID)
-----------------
          2032542

Abgelaufen: 00:00:07.00

Ausführungsplan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=4)
   1    0   SORT (GROUP BY)
   2    1     INDEX (FAST FULL SCAN) OF 'I_RN' (UNIQUE) (Cost=3 Card=1
          034313 Bytes=4137252)