Re: dba_tables.num_rows is less than dba_indexes.num_rows

Not strange at all. As long as you are not doing a full compute, all statistics will be estimates based on sampling. Since the samples are different for the table and each of the indexes, the estimates based on those samples will (almost certainly) differ. The smaller the sample, the greater the swing in the estimates.

At 11:23 PM 8/8/2005, Leng Kaing wrote:
>Hi everyone,
>
>I’m encountering some strange problems with the
>CBO in Oracle 9.2.0.6 – it’s telling me that I
>have more rows in the indexes than there are rows in the tables.
>
>I’ve tried all combinations of dbms_stats and
>analyse and cannot understand how the CBO comes
>up with such numbers. I’ve even done a “delete statistics” and
>Re-analysed the table and indexes but it doesn’t help.
>
>The command I used is variations of the following:
>
> exec
> DBMS_STATS.GATHER_TABLE_STATS(ownname=>'MBS',tabname=>'READINGTOU', -
>
>estimate_percent=>dbms_stats.auto_sample_size,method_opt=>'FOR
>COLUMNS PROCESSSTATUS',degree=>2);
>

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l