Oracle Scratchpad

Partition Stats

It’s possible to spend ages talking about the best ways of collecting, or creating, statistics on partitioned tables.

The possible strategies for maintaining partitioned tables, (exchange partition, split partition, drop partition etc.) the types of partitioning available, and the way that the optimizer plays with the stats as you do so, have kept changing over the years, and I’ve got a large set of examples designed to test what happens to the stats as you do things to the table – but it’s impossible to keep it up to date.

Doug Burns is writing a series of articles about the trials, tribulations, and successes about partitioned tables and statistics. The series was well worth reading and will give you an insight into the problems you may have to address, so I’ve produced a catalogue to make it easy to visit the individual chapters in order.  Make sure you also read the comments and related links.

I thought I’d collate a few other items on partition stats and optimizer behaviour – mainly from Randolf Geist’s blog:

And one from Kerry Osbourne – which lists a new granularity option, and a patch for 10.2.0.4

A couple (as pdf files) from David Kurtz, with a particular view to optimising Peoplesoft.

And an investigation into an oddity with the optimizer when using partitioned indexes