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

At 12:57 PM 6/29/2006, Laimutis Nedzinskas wrote:
> >From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com]
> >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

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