Large (highest ?) Number of Partitions / SubPartitions in *Production*
Date: Sun, 15 Feb 2009 14:58:05 +0800
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 thepartitions, 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