Re: Partitioning Best Practices

From: <>
Date: Wed, 9 Jul 2008 17:14:43 -0700 (PDT)
Message-ID: <>

> You have to ask how likely it is that partition maintenance will be
> delayed for some reason, and what the impact of that will be.
> For partitioning based on dates, where I know that values won't be
> created for future dates, I *always* include an overflow partition
> with MAXVALUE.
> The upside of this is that the application won't fail because it has
> run out of partitions. Given that my number one priority is to have a
> working application, this is a good thing.
> The downside is that my partition maintenance is a bit more complex.
> However, this shouldn't matter, because I do my partition maintenance
> before the overflow partition has any data in it, so I can just drop
> it and add the new partitions. If partition maintenance slips past
> that date and there *is* data in the overflow partition, then I have a
> bit more work to do, but that's better than having the application
> fail.

Hi Phil, thanks for the feedback. I agree that application availability is the most important consideration, thats why I posted the question. Currently I dont use a MAXVALUE partition and I have a regular scheduled PLSQL job to create new partitions. In the event that this maintenance script fails I also have an independant monitoring script which checks how many days of partitioning space is available for inserts. If this is less than 14 days (i.e. two weekly partitions) then I get an email, if we have less than 7 days (i.e. 1 weekly partition) then I get an SMS.

My main concern about the MAXVALUE partition isn't really the partition maintenance because I can simply do a:

alter table <TABNAME> split partition <MAXVALUE> at <DATE> into <NEWPART> , <MAXVALUE> update indexes.

That way the global (unpartitioned) index remains usable and the new local indexes are usable.

My main concern is that having unequal partition sizes could cause the optimizer's partition pruning behaviour to be negatively affected. In your experience does the optimizer use the partition bounds (i.e. VALUES LESS THAN) to make cost calculations, or is it based purely on the statistics for the data contained in the partition..?

In other words does having an empty MAXVALUE partition have any bearing on performance for queries against the partitioned table...?

Thanks for your help.

Matt Received on Wed Jul 09 2008 - 19:14:43 CDT

Original text of this message