Pros/cons of using as many table partitions as possible

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:

1. I'm trying to manage a table that will have about 50 billion rows and about 60 bytes/row.
2. Each row will be partitioned on a year-field for LIST-partitions and a surrogate-key (integer) for HASH-subpartitions.
3. 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.
4. 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).
5. Small subpartition size allows easier replication via partitionswapping.
6. Subpartitions for earlier years can also be compressed and their tablespaces can be made readonly.

What concerns me:

1. Query parsing.
2. Huge amount of system info (objects, segments, extents, DBA_TAB_PARTITIONS, etc.).
3. 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