Re: Partition Type

[Phil Hildebrand]

Can you put in your 'show create table' statement?

Currently, partitioning doesn't support FK's, so you'll probably have to drop that in order to partition the table.

If Entity ID is an integer, then your options are fairly open. You'll probably want to take into account how entity ID is used.

If you just want each entity ID to be part of a different partition, and entity id has a low cardinality (few entity id's), then a list partition might make the most sense, as you can control which entity id's go to which partitions.

LIST: assume 5 entity types:

alter table X partition by list (entity_id)
( partition p1 values in (1), partition p2 ....)

If entity id has a high cardinality, then it might make sense to use hash/key or range partitions. If entity ID is an auto increment, or something similar that has a somewhat predictable max value/distribution then maybe a range partition. If entity ID is grows a lot, and doesn't have a predictable max value, then a key/hash partition is nice because you don't need to know anything about the values of entity to create the partitions...

RANGE: assume up to 2000 entity types:

alter table x partition by range (entity_id)
( partition p1 values less than(500), partition p2 , ... partition p5 values less than MAXVALUE)

HASH/KEY: assume lots of entity types (unknown thousands)

alter table x partition by hash (entity_id) partitions 5

There are probably more technical ways to decide which makes sense for a given table/app, but these are my first conditions I look at when thinking about which way to partition.