Re: Is creating a table with 1000 partitions a stupid thing to do?

I'd say the issue goes a little to what release and OS you are using.

In earlier releases, there is a maximum number of datafiles you can use in a database. IMHO, partitioning without each partition in it's own datafile kind of defeats the the purpose.

As Julio broached, another factor is how many disk drives you have. A large number of partitions with a small number of drives may not help.

As a side road, partitioning is more of a management tool than a performance tool. I work with some fairly large dbs (100GB is small for us, we have a couple in the 1.5TB range with an average in the 750GB range.) With proper indexing and SQL the customer data would be retrieved just as fast with or without partitioning. Partitioning helps with backups (partial exports, etc) and space management, again IMHO. Vertical partitioning might help performance but you appear to be talking about horizontal performance.

In article <37677512.C217A4B3_at_si.unirioja.es>,   Julio Negueruela <julio.negueruela_at_si.unirioja.es> wrote:
> Mike Streeton escribi=F3:
> > =
>
> > We are currently looking to create a very large table >100GB
100,000,00=
> 0
> > rows, the only key we have that is always specified in every query
is a=
>
> > customer id of which there is a range 1-1000. We are looking to
partiti=
> on
> > the table based around this key. Most of the queries will be
trawling t=
> he
> > whole customers data, although some will also specify a date range,
whi=
> ch we
> > can index locally. Not all partitions will be in seperate
tablespaces. =
> This
> > is a reporting database with a large number of online connected
users
> > running canned queries.
> > =
>
> > Questions:
> > Is there a better way of doing this?
> > What is the overhead of having a large number of parititions?
> > Would standard index/clusters work better?
>
> Hi, Mike
>
> Making partitions you've got an "implicit" index on partitioning key
and
> the I/O accesses will be spread over the different disks where you
> locate the tablespaces' datafiles. If you've got partitions in the
same
> tablespace you'll approach the first advantage but not the second one.
> Another possibility is to group all possible partitions you want to
> locate in the same tablespace in one partition, but you should fist
see
> if this one is not big enough to loose performance you win with
having a
> lot of "little" partitions. That is, if the resulting "big" partition
is
> not very big so the scanning of it for searching some row is not very
> expensive in terms of time comparing to having very little partitions.
>
> Hope this help and you understand what I mean (apologize for my
horrible
> English).
> -- =
>
> Julio Negueruela
> DBA Servicio Inform=E1tico
> Universidad de La Rioja - Spain
> Telf: 941-299179 Fax: 941- 299180
> mailto:julio.negueruela_at_si.unirioja.es
>

--
Joseph R.P. Maloney, CCP,CSP,CDP
MPiR, Inc.
502-451-7404
some witty phrase goes here, I think.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Jun 16 1999 - 08:13:35 CDT