Posted by Piero 'Giops' Giorgi on 06/05/07 21:10

> Do I understand it correctly that you already have 50 partitions, and
> now you want even more? About what size do you expect per partition?
>
> I'm not sure that partitioning by state is the best strategy. The partition
> for California will be a lot bigger than the ones for Alaska and Rhode
> Island.

I know that, but partitioning by county makes the DB a lot easier to
maintain.
I have to work that way because I'm dealing with criminal records, and
they are separated by county with a ton of different files, so for
many of them I have to clear the table and reload the whole county
every time I get an update. Easier on partitions... :-)

Table size can be anywhere from 8000 to 3 million records, depending
on the county.

The best way to do that would be having a table partitioned over 3077
filegroups, so storing the data will go by COUNTY in this way:

CA_ALAMEDA
CA_ALPINE
CA_AMADOR
CA_BUTTE
CA_CALAVERAS
CA_COLUSA
CA_CONTRA_COSTA
CA_DEL_NORTE
CA_EL_DORADO
CA_FRESNO
CA_GLENN
CA_HUMBOLDT
CA_IMPERIAL
CA_INYO

With the COUNTY as the partition Parameter.

But, before trying, can I have 3077 files in ONE partition, and drop
all the states stuff?

Thanks!

Piero

[Back to original message]