Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: dba_tables.num_rows is less than dba_indexes.num_rows
Wolfgang,
This is all news to me...
Are you saying that FOR ALL INDEX COLUMNS or FOR COLUMNS only gathers histograms for the tables, but not the stats? So do I have to do it in 2 steps - gather stats, then gather histograms?
3) I've never computed on any of the systems that I've worked on. And it certainly cannot be done on the current database as it would take too long. I always thought an estimate would be good enough. Now I'm hearing that only a compute will do. ARGH!! We cannot afford to do this.
Tel: +61-3-9840-3832
Fax: +61-3-9840-3102
-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com]
Sent: Tuesday, 9 August 2005 11:45 PM
To: Leng Kaing
Cc: oracle-l_at_freelists.org
Subject: 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-lReceived on Thu Aug 11 2005 - 00:38:12 CDT