RE: dba_tables.num_rows is less than dba_indexes.num

From: Leng Kaing <Leng.Kaing_at_hsntech.com>
Date: Thu, 11 Aug 2005 15:34:50 +1000
Message-ID: <18D551B1B928FF47A65B2D91F705906A017BACC7@HSNDON-EX01.hsntech.int>

Wolfgang,

This is all news to me...

I always thought that NUM_ROWS is important. I know that a few weeks ago when I noticed the differences in the num_rows in tables vs indexes, we manually increased the table's num_rows and the query would revert to using an index rather than a full table scan. I have yet to do a test to see if what would happen if I set NUM_ROWS to a small number, eg. 5.
I also did "..., estimate_percent=>dbms_stats.auto_sample_size, method_opt=>'FOR ALL INDEXED COLUMNS', degree=>2, cascade=>true"

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.

Leng Kaing
Hansen Technologies
2 Frederick St; Doncaster VIC 3108

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-l

Received on Thu Aug 11 2005 - 00:38:12 CDT

RE: dba_tables.num_rows is less than dba_indexes.num_rows