Fwd: Num of Partitions with Subpartitions - quick newbie question

That's not a partitioning issue - that's an ILM issue. Organizing the tablespaces monthly would mitigate it, even if the data were partitioned daily.

> At about 10,000 tablespace count the database startup and shutdown time got
> very slow in the range of 10 to 15 minutes (absolutely value will vary with
> hardware but it is proportional to number of database files). Also it took
> a lot of time to backup 10,000 small tablespaces (one file per tablspace)
> as compared to backing up 1000 large tablespaces.
>

To answer the question with a question: is there significant data skew between Organization IDs, and do you ever have queries which span those Organization IDs? If the first, be very careful how you partition and subpartition them - Organization IDs that are queried together and exhibit similar data skew should be in the same partition, because:

1. Organization ID 714's wierdo data skew could get lost in the wash, but
2. A query which spans two Organization ID's in different partitions will use global (or next-level-up) statistics.

>> http://docs.oracle.com/cd/E11882_01/server.112/e17118/statements_3001.htm#i2085640
>> On Fri, Dec 14, 2012 at 9:59 AM, <Christopher.Taylor2_at_parallon.net<mailto:
>> Christopher.Taylor2_at_parallon.net>> wrote:
>> We're experimenting with partitioning a table into many partitions with
>> many subpartitions because of the way we process by Organization IDs and
>> Secure Profiles - related to parallel application server processing.
>> Is there a MAXIMUM (or recommended value to keep under) for number of
>> partitions and/or subpartitions?
>>
>> This table (if design works) would have approximately 8000 subpartitions
>> and 1/4 of that in partitions I think. (I don't have the numbers off the
>> top of my head)
>>

-- 
Adam Musch
ahmusch_at_gmail.com
--
http://www.freelists.org/webpage/oracle-l