Re: Partitioning Best Practices

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 09 Jul 2008 12:47:22 -0700
Message-ID: <1215632829.459305@bubbleator.drizzle.com>


mccmx_at_hotmail.com wrote:
> 10gR2 EE with Partitioning.
>
> I'm interested to hear peoples opinions on the following scenario. I
> have a 500 million row table which is partitioned by date (weekly).
>
> I'm trying to decide between specifying a maxvalue partition or not.
>
> Advantages of MAXVALUE:
>
> If we run out of partitions we have a catchall partition to prevent
> application failure.
>
> Disadvantages of MAXVALUE:
>
> Partition maintenance becomes more complex - i.e. I can no longer ADD
> PARTITION, I have to SPLIT the MAXVALUE partition.
> Global and Local indexes will need to be rebuilt after splitting the
> MAXVALUE partition if it contains rows.
> Having a MAXVALUE partition defined will mean that the partitioning
> scheme is no longer equal. This could potentially affect the
> optimizer and partition pruning.
>
> What is the general consensus in the Oracle community about whether to
> specify a MAXVALUE partition or not.
>
> Are people generally using MAXVALUE for range based partitioning..?
> If so, how have you overcome the problems above.
>
> Thanks

Using a MAXVALUE partition makes it impossible to insert a different partition that logically follows the one preceding it: Thus you are in a dead-end situation. The best solution is no MAXVALUE and to use a regularly scheduled job to add partitions on a regular basis. Make sure the job checks for skipped partitions before adding the next one.

Note to everyone: There is an ugly bug with INTERVAL partitioning in 11gR1 so do not use it. The partitioning works perfectly. Querying SELECT * works perfectly. But query SELECT COUNT(*) and you will think you are looking at an empty table.

This should be fixed very soon.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Wed Jul 09 2008 - 14:47:22 CDT

Original text of this message