Re: Pros/cons of using as many table partitions as possible

On Apr 18, 5:30 pm, Paulukas <paulu..._at_sover.net> wrote:
> Oracle 9i had a limit of 64K-1 partitions per table.  10g increased
> that to over a million (1024K-1).  Is it practical to approach that
> limit (>500,000 partitions)?  What are the pitfalls?
>
> I'm very aware of partition-elimination and partition-wise joins.
> Assume all queries will appropriately specify the partition-keys.
>
> Here are some of the requirements and goals:
>   a) I'm trying to manage a table that will have about 50 billion rows
> and about 60 bytes/row.
>   b) Each row will be partitioned on a year-field for LIST-partitions
> and a surrogate-key (integer) for HASH-subpartitions.
>   c) I'd like to use composite list/hash partitioning to support:
>        - Easy removal of old data (dropping partitions based on year)
>        - Minimal size of subpartitions due to large hash count.
>   d) With small-enough subpartition size, I won't need any indexes,
> since a full scan of an individual subpartition should be very
> reasonable.  This will save an enormous amount of space (no indexes).
>   e) Small subpartition size allows easier replication via partition-
> swapping.
>   f) Subpartitions for earlier years can also be compressed and their
> tablespaces can be made readonly.
>
> What concerns me:
>   a) Query parsing.
>   b) Huge amount of system info (objects, segments, extents,
> DBA_TAB_PARTITIONS, etc.).
>   c) Other things I do not know about!!  :-)
>
> I welcome other suggestions for managing such a large table, but
> please try to address the original question about using a very high
> number of partitions.
>
> Thanks.
>
>      - Paul

I do not have personal experience with partitioned tables with 100's of partitions or more but I have seen several posts on various Oracle forums where the poster had problems that could be traced to the fact the table was in a large number of partitions. You might want to check the archives at Metalink, OTN, and for Oracle-L. There were operations that had to look at every partition hence the more partitions the more time these operations took.

Personally I would want to use the fewest number of partitions that provide acceptable query plan row elimination for my application needs.

Obviously the type of partitioning and the number and type of queries that do not use the partition key would have a big effect on how many partitions are enough verse perhaps too many.

HTH -- Mark D Powell -- Received on Sat Apr 19 2008 - 07:57:47 CDT