Partitioning Best Practices

[mc...@hotmail.com]

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

[Michael Austin]

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.

[DA Morgan]

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
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[phil_h...@yahoo.com.au]

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.

-- Phil

[mc...@hotmail.com]

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

[mc...@hotmail.com]

> 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.

Are you sure...? What about SPLIT PARTITION which will add a new
partition in between existing partitions:

alter table <TABNAME> split partition <MAXVALUE> at <DATE> into

<NEWPART> , <MAXVALUE>;

[phil_h...@yahoo.com.au]

On Jul 10, 10:14 am, mc...@hotmail.com wrote:

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

All the plans I've seen have partition pruned correctly - i.e., the
existence of the overflow partitions haven't influenced them.

-- Phil

[DA Morgan]

I was just referring to ADD.