dba_tables.num_rows is less than dba_indexes.num

From: Leng Kaing <Leng.Kaing_at_hsntech.com>
Date: Tue, 9 Aug 2005 15:23:40 +1000
Message-ID: <18D551B1B928FF47A65B2D91F705906A017BAC92@HSNDON-EX01.hsntech.int>

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);

I've even used estimate_percent of 50 and still getting lower numbers for the table.

Initially I was afraid that since the index is larger than the table, the index would never be used. So the question is, does it really matter that the indexes' num_rows is bigger than the tables' num_rows? What is the consequence of this? And how do I get the optimizer to correct the differences in the stats. The table is 30G in size and growing, so a COMPUTE is out of the question.

TIA, Leng.

TABLE_NAME                       NUM_ROWS AVG_ROW_LEN LAST_ANALYZED



------------------------------ ---------- -----------

--------------------


READINGTOU                      248472177          92 09-AUG-2005