Re: Subject: Large Tables, Bad Indexes and Fake Statistics

Hi Wolfgang, Don,

>Since the partition is pre-created it get its statistics gathered
>(all zero rows) and therefore dynamic sampling at level 2 (all tables
>without statistics) won't get called.

This is of course fine. I understand the statistics gathering policy in a different way.

>> The partition is created a few days before it will first be loaded into.
>> We have a recurring problem when, after the partition is loaded into
>> (2-3 million records per load), ..
>>After I gather stats
>>on the table (takes 2.5 hours) the queries then use the PK as desired.

i.e the partition is created, loaded but not analyzed. In that case I thing the dynamic sampling (on level 2) can produce some misleading partition statistics due to the small sample size.

>I've been seeding partition statistics without problems.
>Usually I clone them from an existing partition - replacing the
>low/high values of the partitioning column(s).

good idea! cloning the partition statistics should bridge the time intervall until the real partition statistics are gathered.

regards,

Jaromir

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