Re: Num of Partitions with Subpartitions - quick newbie question

From: Paresh Yadav <yparesh_at_gmail.com>
Date: Sun, 16 Dec 2012 22:31:28 -0500
Message-ID: <CAPXEL0K8epNLS6VRqjznA5bRLsO572FyHYGoUrFyBTPb-xNDZQ_at_mail.gmail.com>

Even though physical limits might be much bigger as Andy has pointed out usually you don't want to go beyond 10,000 for practical reasons as I have experienced (in my specific circumstances): We had about 25 partitioned tables (no sub partition),1 partition per day. Due to large size of the database we decided to keep each partition in its own tablespace for ability to drop the tablespace including contents at the end of 7 year period when the old data needed to be purged, to make the tablespaces READONLY and hence backup infrequently etc..

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.

Regards,
Paresh

On Fri, Dec 14, 2012 at 11:49 AM, <Christopher.Taylor2_at_parallon.net> wrote:

> Thanks Andy - much appreciated.
> Chris
>
> From: andyklock_at_gmail.com [mailto:andyklock_at_gmail.com] On Behalf Of Andy
> Klock
> Sent: Friday, December 14, 2012 9:21 AM
> To: Taylor Christopher - Nashville
> Cc: oracle-l_at_freelists.org
> Subject: Re: Num of Partitions with Subpartitions - quick newbie question
>
> Depends on your version. It used to be 64K (total partition/sub
> partitions) but in versions 10g/11g it is 1024K (minus 1). So with 8000 you
> are only getting started :)
>
>
> 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)
>
> Regards,
>
>
> Chris Taylor
> Oracle DBA
> Parallon IT&S
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Thanks
Paresh
416-688-1003


--
http://www.freelists.org/webpage/oracle-l

Received on Mon Dec 17 2012 - 04:31:28 CET