RE: dba_tables.num_rows is less than dba_indexes.num_rows
- From: "Leng Kaing" <Leng.Kaing@xxxxxxxxxxx>
- To: "Wolfgang Breitling" <breitliw@xxxxxxxxxxxxx>
- Date: Tue, 9 Aug 2005 16:11:16 +1000
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@xxxxxxxxxxxxx]
Sent: Tuesday, 9 August 2005 4:09 PM
To: Leng Kaing
Cc: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
