Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: dba_tables.num_rows is less than dba_indexes.num_rows

RE: dba_tables.num_rows is less than dba_indexes.num_rows

From: Leng Kaing <Leng.Kaing_at_hsntech.com>
Date: Tue, 9 Aug 2005 16:11:16 +1000
Message-ID: <18D551B1B928FF47A65B2D91F705906A017BAC96@HSNDON-EX01.hsntech.int>


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
Received on Tue Aug 09 2005 - 01:13:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US