Fwd: Num of Partitions with Subpartitions - quick newbie question
- From: Adam Musch <ahmusch@xxxxxxxxx>
- To: Oracle Freelist <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 17 Dec 2012 13:53:12 -0600
Forwarding to the list, because I am an idiot.
-------------------------------------
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@xxxxxxxxxxxx<mailto:
>> Christopher.Taylor2@xxxxxxxxxxxx>> 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@xxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Fwd: Num of Partitions with Subpartitions - quick newbie question - Adam Musch
