RE: full-scan vs index for "small" tables

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: Laimutis.Nedzinskas@xxxxxxxxxxxxx
  • Date: Thu, 29 Jun 2006 18:18:45 -0600

At 12:57 PM 6/29/2006, Laimutis Nedzinskas wrote: 

>From: Wolfgang Breitling [mailto:breitliw@xxxxxxxxxxxxx]
>This is probably your problem right there. Earlier you were claiming
that you
had a histogram on the type (status?) column. Your above
gather_table_stats call
does not create a histogram on any columns of TEST_CBO. "FOR ALL COLUMNS
SIZE
AUTO" creates histograms ONLY on columns that have been used in a
predicate.
Since this is a brand new table none of the columns have of course been
used in
a predicate yet, so no histograms are created.


[snip]


This is exactly what I noticed before: plan may change just like that
w/o no reason. It's enough just to issue explain plan several times. Is
2 a magic number? May be.

It's not for no reason. The reason is that you gather statistics with method_opt=>'for all columns size auto'. If you don't want this kind of surprise - or others related to statistics - take control of the statistics gathering. Control
- when: date and times
- what: which tables, indexes, and columns
- how: what sampling rate, what method_opt and other parameters.

You don't just take init.ora defaults, why would you accept the statistics gathering defaults?


Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com 


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


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 06-2006