Take the 2-minute tour ×

is there a canonical way to partition a table by referenced data to another table?

for example


   timetable_id -- foreign key
   .. other data ..

i want to partition bigtable by the datetime in timetable. thankx.

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

As noted here, he partitioning column must be a part of all unique indexes on the table, which would include your primary key.

I think your only choice here would be to denormalize the timetable.datetime column into bigtable so that the column is available for partitioning.

share|improve this answer
add comment

Partitioning key must be a column of the table being partitioned. If you have a correlation between id and datetime in timetable then you can partition by that id:

id          datetime
1           20091001
2           20091002
3           20091003
32          20091101
33          20091102
62          20091201

then the partitioning ranges (2009-10-01, 2009-10-31), (2009-11-01, 2009-11-30) etc can be expressed in terms of id values: (1,31), (32,62), ... However, this requires that the rank of the id values matches exactly the rank of the datetime values. If you don't have this correlation, then you must either move the datetime column in the bigtable, or rearrange your id so that they correlate wqitht he datetime.

share|improve this answer
unfortunately id is a uniqueidentifier, so it wont have that property –  Timmy Nov 10 '11 at 0:52
add comment

Your Answer


