Re: Large Tables, Bad Indexes and Fake Statistics

Hello Don,

Two thoughts that comes immediately into my mind:

1. Dynamic Sampling
   > The partition is created a few days before it will first be loaded into.
   .
   > After I gather stats on the table (takes 2.5 hours) the queries then use
   > the PK as desired.
   .
   > Anyway, my theory is that the CBO recognizes that, after the load, the
   > data is
   > drastically different than the statistics.
   

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)?

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)

Regards,

Jaromir D.B. Nemec

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