Sql-server – Mechanics of partitioning

Well, I'm not saying this is pretty, but it might work.

First, partition the instance table by pseudo-datetime. Each day/week/month, load the new instance information. Then assign a new primary key id to this data. Normally, you would want this to be an identity, but in this case, you have to guarantee uniqueness. Now, place this into its own partition.

This is almost by date time. However, other tables that reference instances will be able to join to the correct partition.

Now, if the events and locations are relatively small tables, then perhaps this is enough.

Otherwise, you can repeat for these tables. However, it gets harder if you add a new event that refers to an older instance. To handle this, you'll want to assign a new event id range to each partition, but leave a big gap at the end. Then, when you load event data, you need to determine the correct partition to place it in, based on the partition of the instance id. You may end up loading events into multiple partitions.

This is an idea.

You can't use interval partitioning with reference partitioning, however, It's possible to mix range partitioning and reference partitioning, as shown in the example of reference partitioning in the 11g doc:

The benefit of this extension is that tables with a parent-child relationship can be logically equipartitioned by inheriting the partitioning key from the parent table without duplicating the key columns. The logical dependency also automatically cascades partition maintenance operations, thus making application development easier and less error-prone.

The child table doesn't need to contain the partition key (here the log date). It inherits the partition key from the parent table.

Range partitioning involves a bit more maintenance because you have to create the partitions yourself. However, once the partitions are created, range and interval work similarly.