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 -- //www.freelists.org/webpage/oracle-l