Re: Partitioning Best Practices

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
>

IMO using a MAXVALUE for something like date would not be very optimal as you have already stated, eventually all data would be stored in that partition. If you are going to be pruning over time, I would stick with what you have, however, might do it by month rather than week - depending on how much data you want to have available and for how long.

For the same reason stated above, MAXVALUE would be good for something like REGION or <pick your own> where the number of rows that would fit in this category would be minimal. Received on Tue Jul 08 2008 - 19:43:21 CDT