Pros/cons of using as many table partitions as possible
Date: Fri, 18 Apr 2008 14:30:02 -0700 (PDT)
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:
- I'm trying to manage a table that will have about 50 billion rows and about 60 bytes/row.
- Each row will be partitioned on a year-field for LIST-partitions and a surrogate-key (integer) for HASH-subpartitions.
- 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.
- 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).
- Small subpartition size allows easier replication via partitionswapping.
- Subpartitions for earlier years can also be compressed and their tablespaces can be made readonly.
What concerns me:
- Query parsing.
- Huge amount of system info (objects, segments, extents, DBA_TAB_PARTITIONS, etc.).
- 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.