Re: Large Tables, Bad Indexes and Fake Statistics

A few clarifications:
* The table is range partitioned on two number fields (year, month), not a single date field. These fields are two of the four fields that are most commonly used in query predicates. * Both the PK and second index are LOCAL.

My replies are inline below.

> 1. Dynamic Sampling
>
> Do you use dynamic sampling? What level? Could it be the case that the
> missing partition statistics gathered using dynamic sampling are inaccurate
> due to extremely small sample size (small number of blocks red in sampling)?

We are using level 2 dynamic sampling (the default in 10g, iirc).

> 2. BV Peek
>
> Additionally to the well-known histogram trap of the BV peek there is a less
> known data warehouse analogy. It comes in case of fact tables with loaded
> and preallocated partitions. The problem appears if the peek uses the
> statistics of one type of partition (e.g. of the empty one) but the
> statement run on the partition of the other type (e.g. on the full one).
> If I understand you correctly that your problem disappears if the statistics
> of all partitions are more or less the same (and the statement uses bind
> variable on the partition key), I'd also check this possibility.
> There is a rather funny example of this kind of trap documented on
> http://www.jlcomp.demon.co.uk/faq/bind_peek.html
> (The details probably differs from your case)

I've considered this possibility. Jonathan's text there certainly hits close to my mark. Rolling window, pre-allocated empty partitions, etc.

Although, the problem also affects test queries using literals as well. And once statistics are up-to-date, there is no problem. e.g. after the first market is loaded, that "current" partition will have 1-to-3 million rows, the other 3 partitions will have ~30 million rows each.

-- 
Don Seiler
oracle blog: http://ora.seiler.us
ultimate: http://www.mufc.us
--
http://www.freelists.org/webpage/oracle-l