At 01:05 AM 6/20/2007, FreeLists Mailing List Manager wrote:
The problem: A partition of FOO is bulk loaded around 10 times a month. 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), queries on FOO that have A, B, C, D in the WHERE clause will start using the second index. The fact that this second index doesn't use column B means it is walking over a HUGE set of records, practically a complete partition scan. It seems like an out-of-whack statistics issue. After I gather stats on the table (takes 2.5 hours) the queries then use the PK as desired. To fight this, we had just this month started calling dbms_stats.set_table_stats and set_index_stats to indicate a full month's worth of stats before loading. Then for the rest of the day we could fool Oracle, until the nightly stats job then set the real stats. However this didn't work yesterday. I'm not sure why, but I did notice that the dba_tab_statistics table showed a sample_size for my user-set stats, when I don't believe there was one before.
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. The setting of statistics ought to work. Do/did you also set the column statistics? You should find out why it didn't work "yesterday". 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).
Regards Wolfgang Breitling Centrex Consulting Corporationwww.centrexcc.com