Large (highest ?) Number of Partitions / SubPartitions in *Production*

From: Hemant K Chitale <>
Date: Sun, 15 Feb 2009 14:58:05 +0800
Message-Id: <>

In theory, the maximum number of Partitions/SubPartitions that Oracle supports for a table is 1024K-1 in 10gR2. Per MetaLink Note#76765.1, the limit was 64K-1 in 8i -- and, I understand from the 9i docs, was also the same limit in 9i. The Note candidly admits that only 1,000 Partitions have been tested.

Have you implemented / seen implemented, say 64thousand Partitions for a table (and this being done for possibly half-a-dozen tables), with at leaste one corresponding LOCALly Partitioned Index ?

What are the implications on :

1. MetaData in the Shared Pool and Latching.
2. Gather_Stats (done GLOBAL or PARTITION wise).
3. Queries that, very unfortunately, end up scanning all the 
partitions, many of which are small 64K partitions only.

Hemant K Chitale

"A 'No' uttered from the deepest conviction is better than a 'Yes' merely uttered to please, or worse, to avoid trouble." Mohandas Gandhi Quotes

Received on Sun Feb 15 2009 - 00:58:05 CST

Original text of this message