Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Is creating a table with 1000 partitions a stupid thing to do?
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