Partitioning Best Practices

From: <>
Date: Tue, 8 Jul 2008 16:37:08 -0700 (PDT)
Message-ID: <>

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 Received on Tue Jul 08 2008 - 18:37:08 CDT

Original text of this message