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 creating a table with 1000 partitions a stupid thing to do?

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

From: Julio Negueruela <julio.negueruela_at_si.unirioja.es>
Date: Wed, 16 Jun 1999 11:57:38 +0200
Message-ID: <37677512.C217A4B3@si.unirioja.es>


Mike Streeton escribió:
> =

> 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ático

Universidad de La Rioja      -      Spain
Telf: 941-299179     Fax: 941- 299180

mailto:julio.negueruela_at_si.unirioja.es Received on Wed Jun 16 1999 - 04:57:38 CDT

Original text of this message

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