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

Mike,

  You seem to have a misconception about partitions. The idea behind using partitions is simply to subdivide your I/O across available disks to make your queries faster.

  Since most of your queries are gonna trawl across all the data of a user, it makes sense to distribute the data of each user across all disks. So choose some other field for partitioning since larger no of partitions does not necessarily give a faster query.

  About ur questions :

1. A better way is to partition using a field that ensures that data for EACH user is spread across ALL available disks.
2. The data, conditions etc for each partition are stored in SYSTEM tablespace, so more partitions means more area used there as well as more updates when u do something to the table plus more entries to keep track of.
3. Clusters wud help u only for multiple tables, not for a single table. THe best bet wud be to split the table as in point 1 above and then index locally on the fields u want.

  Hope this helps,

Chetan
chetanw_at_writeme.com

In article <376769ff_at_kaos.fastnet.co.uk>,   "Mike Streeton" <mikestreeton_at_yahoo.com> wrote:
> We are currently looking to create a very large table >100GB
100,000,000
> 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
partition
> the table based around this key. Most of the queries will be trawling
the
> whole customers data, although some will also specify a date range,
which 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?
>
> Many Thanks
>
> Mike
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Jun 18 1999 - 17:12:38 CDT