RE: dba_tables.num_rows is less than dba_indexes.num_rows

Ok, thanks again.

If only there was a simple answer!   

-----Original Message-----
From: Christian Antognini [mailto:Christian.Antognini_at_trivadis.com] Sent: Thursday, 11 August 2005 6:21 PM
To: Leng Kaing
Cc: oracle-l_at_freelists.org; Wolfgang Breitling Subject: RE: dba_tables.num_rows is less than dba_indexes.num_rows

Leng

>Still don't understand part 1 :-( Are you saying that
>num_rows is important on a table, but not on an index?

num_rows on a table is definitively used by the CBO to estimate costs.

Neither Wolfgang nor I know a situation where the num_rows of an index is used by the CBO. If you, or somebody else, know such a situation, please, share it! (Of course we would like to see a reproducible test case...)

>If I manually updated the table's num_rows to be
>bigger than then the indexes' num_rows, and saw
>that the CBO is now favouring an index look up
>rather a full table scan, surely it would prove that
>num_rows is important for both tables and indexes?

No. It only proves that table's num_rows is important.

>So short of doing a compute, how do we give the CBO
>good stats to work with?

As I wrote it depends... and if you need histograms in some situation even a compute is not enough!

HTH,
Chris

--
http://www.freelists.org/webpage/oracle-l