dba_tables.num_rows is less than dba_indexes.num_rows
- From: "Leng Kaing" <Leng.Kaing@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 9 Aug 2005 15:23:40 +1000
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
12:15:56
INDEX_NAME NUM_ROWS DISTINCT_KEYS LAST_ANALYZED
------------------------------ ---------- -----------
--------------------
XIE10READINGTOU 237926990 2 08-AUG-2005
19:38:03
XIE11READINGTOU 0 0 08-AUG-2005
19:37:35
XIE12READINGTOU 242603972 3 08-AUG-2005
19:37:35
XIE14READINGTOU 252649756 5 08-AUG-2005
19:37:06
XIE15READINGTOU 260990285 21001019 08-AUG-2005
19:31:46
XIE16READINGTOU 246922813 1268 08-AUG-2005
19:36:35
XIE3READINGTOU 244141626 18 08-AUG-2005
19:35:53
XIE4READINGTOU 0 0 08-AUG-2005
19:35:24
XIE5READINGTOU 0 0 08-AUG-2005
19:35:24
XIE7READINGTOU 255999687 22046532 08-AUG-2005
19:35:24
XIE9READINGTOU 0 0 08-AUG-2005
19:28:43
XPKREADINGTOU 246700411 246700411 08-AUG-2005
19:28:43
13 rows selected.
----------------------------------------------
Leng Kaing
Hansen Technologies
2 Frederick St; Doncaster VIC 3108
Tel: +61-3-9840-3832
Fax: +61-3-9840-3102
Other related posts:
