SQL>create table t1 as select trunc(ora_hash(level)/1000000) a, rpad('-', 1000, '-') b, rpad('-', 1000, '-') c, rpad('-', 1000, '-') d, rpad('-', 1000, '-') e, rpad('-', 1000, '-') f from dual connect by level <= 1000000;

Table created.

SQL>create index i1 on t1(a);

Index created.

SQL>call dbms_stats.gather_table_stats(ownname=>'', tabname=>'T1');

Call completed.

SQL>select * from (select a, count(1) c from t1 group by a order by 2 desc) where rownum < 20;
         A          C
---------- ----------
      1964        299
       382        287
      2332        285
       803        284
       816        283
      1633        282
       550        281
       960        281
      4149        280
      2572        280
      3493        279
      2189        278
       145        277
      1725        276
       153        275
       949        275
      2987        274
      3087        274
      3264        273

19 rows selected.

SQL>select min(c), max(c) from (select count(1) c from t1 group by a);
    MIN(C)     MAX(C)
---------- ----------
       179        299

SQL>select count(distinct a) from t1;
COUNT(DISTINCTA)
----------------
            4295

SQL>explain plan for select count(distinct a) from t1;

Explained.

SQL>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 405047221

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     1 |    13 |   271K  (1)| 00:54:22 |
|   1 |  SORT AGGREGATE      |          |     1 |    13 |            |          |
|   2 |   VIEW               | VW_DAG_0 |  4295 | 55835 |   271K  (1)| 00:54:22 |
|   3 |    HASH GROUP BY     |          |  4295 | 17180 |   271K  (1)| 00:54:22 |
|   4 |     TABLE ACCESS FULL| T1       |  1000K|  3906K|   271K  (1)| 00:54:21 |
---------------------------------------------------------------------------------

11 rows selected.