Re: dba_tables.num_rows is less than dba_indexes.num_rows

I could be wrong, of course, but I am not aware of any situation where the num_rows number in the index statistics is being used by the CBO. The index statistics to watch out for rae leaf_blocks, distinct_keys, and the one which has the greatest impact on whether an index is being used or not, clsutering_factor.

You said you are using variations of
  "..., estimate_percent=>dbms_stats.auto_sample_size, method_opt=>'FOR COLUMNS PROCESSSTATUS', degree=>2"

That variation actually gathers a histogram on the processstatus column, not really table statistics - although it (unfortunately) updates the num_rows statistic.

IMO, gathering a histogram with anything but compute is wrought with danger. You collect a histogram because the column's data distribution is skewed. If you collect statistics by sampling you are overestimating the frequently occurring values and underestimating - or missing alltogether - rare values.

Leng Kaing wrote:
> 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.
>
>

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l