Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: dba_tables.num_rows is less than dba_indexes.num_rows

RE: dba_tables.num_rows is less than dba_indexes.num_rows

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Thu, 11 Aug 2005 09:30:42 +0200
Message-ID: <F2C9CCA71510B442AF71446CAE8AEBAF0AC032@MSXVS04.trivadis.com>


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:

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
Received on Thu Aug 11 2005 - 02:32:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US