RE: dba_tables.num_rows is less than dba_indexes.num_rows

Thanks very much for the clarifications, Chris, esp. on part 2 and 3.  

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

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?  

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

Leng Kaing

Hansen Technologies

2 Frederick St; Doncaster VIC 3108  

Tel: +61-3-9840-3832

Fax: +61-3-9840-3102    

From: Christian Antognini [mailto:Christian.Antognini_at_trivadis.com] Sent: Thursday, 11 August 2005 5:31 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  

Hi Leng  

>1) 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.
 

As you wrote you modified num_rows for the table, not for the indexes... Wolfgang wrote about the num_rows of the indexes.  

>2) 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?
 

With 'FOR ALL INDEXED COLUMNS' you will have:

• table statistics
• column statistics and histograms only for the indexed columns
• no column statistics on non-indexed columns!

Usually gathering statistics only for indexed columns is bad. In fact there are plenty of predicates on not-indexed columns (at least in almost all applications that I have seen so far...).  

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

You don't need to compute in all situations. And, as you wrote, it is not always possible. Of course with estimations you could miss some information, that's all. If it does happen you have two possibilities: 1) compute 2) setting histograms manually.  

Personally I start with small estimates and only if the statistics are not good I increase the estimate percent. Notice that "not good" means "doesn't correctly describe the data".    

Regards,

Chris

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