Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: is there limit to number of partitions in a table

Re: is there limit to number of partitions in a table

From: Mark <simmons_mark_at_yahoo.com>
Date: 10 Mar 2004 08:12:58 -0800
Message-ID: <5366fb41.0403100812.45ccf303@posting.google.com>


Colin,

104 partitions shouldn't be a problem. I think it's pretty normal to setup a scenario like the one you are talking about, especially if you think your reporting will be done week over week. I would recommend that you consider having 105 partitions instead.

If you don't need the range scans and actually query by the exact date, you could hash partition the data. In that scenario, I believe that you would want to partition by some factor of 2 in order to get an even balance among partitions. The advantage of using a hash partitioned table is that you don't have to write/schedule all of the roll-off scripts. (Remember, you can't do range scans.)

You might also want to look at other related tables that could be equi-partitioned before you settle on weeks instead of months.

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings, Southlake, TX

colin_lyse_at_yahoo.com (colin_lyse) wrote in message news:<404e4963$0$29937$45beb828_at_newscene.com>...
> we have a very large table (starting at 1 billion growing by 4 million a day
> we will keep 2 years - 2 billion rows) that has to be partitioned. Is there a
> limit ot number of partitions, not necessarily a physical limit, more of a
> partical limit. We are thinking of creating ~104 partitions (1 per week, 20
> million rows), is that execessive.
>
> what if we subpartion each of those by say 4 partition giving us 416
> partitions is that execessive will it cause problems for us?
Received on Wed Mar 10 2004 - 10:12:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US