scott@9i > select job,count(*) from emp group by job;

JOB         COUNT(*)
--------- ----------
ANALYST        65536
CLERK         131072
MANAGER        98304
PRESIDENT      32768
SALESMAN      131072

scott@9i > set autotrace traceonly exp
-- First collect statistics without proper histograms
scott@9i > exec dbms_stats.gather_table_stats('SCOTT','EMP');

PL/SQL procedure successfully completed.


-- this query should produce a card of approximately 32768
-- But it produces something else.

scott@9i > select count(*) from emp where job='PRESIDENT';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=261 Card=1 Bytes=8)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=261 Card=91750 Bytes=
          734000)


--
-- Collect stats with histograms
-- You get exactly what you want.
-- This is just an example. In reality you may want to collect histogram 
-- only on indexed columns with a proper bucket size.
--
scott@9i > exec dbms_stats.gather_table_stats('SCOTT','EMP',METHOD_OPT=>'FOR ALL COLUMNS SIZE 250');

PL/SQL procedure successfully completed.

scott@9i > select count(*) from emp where job='PRESIDENT';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=261 Card=1 Bytes=8)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=261 Card=32768 Bytes=
          262144)

scott@9i > compute AVG SUM of "count" on report
scott@9i > break on report
scott@9i > select job,count(*) "count" from emp group by job;

JOB            count
--------- ----------
ANALYST        65536
CLERK         131072
MANAGER        98304
PRESIDENT      32768
SALESMAN      131072
          ----------
avg          91750.4
sum           458752