RE: dba_tables.num_rows is less than dba_indexes.num_rows

So does it matter that the indexes' num_rows are bigger than that of the table? I'm concerned that some indexes will never be used as the CBO will think it's bigger than the table or some of the other indexes.

-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com] Sent: Tuesday, 9 August 2005 4:09 PM
To: Leng Kaing
Cc: oracle-l_at_freelists.org
Subject: 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