Both range and hash partitioning allow users to specify as many as 31 columns as partition keys, creating a composite partition key. For hash partitions, the partition key behaves as expected, determining how individual data rows are distributed to different partitions. However, the partition keys for range partitions can behave unexpectedly, especially when the partition keys are numeric. The unexpected behavior occurs because Adaptive Server uses the fewest partition keys possible in the sequence until it determines the appropriate partition, instead of using all the keys each time to determine where the row should be stored. The following SQL text describes the rule for determining partition keys:
if key1 < a, then the row is assigned to p1 if key1 = a, then if key2 < b or key2 = b, then the row is assigned to p1 if key1 > a or (key1 = a and key2 > b), then if key1 < c, then the row is assigned to p2 if key1 = c, then if key2 < d or key2 = d, then the row is assigned to p2 if key1 > c or (key1 = c and key2 > d), then if key1 < e, then the row is assigned to p3 if key1 = e, then if key2 < f or key2 = f, then the row is assigned to p3 if key2 > f, then the row is not assigned
Which is summarized as:
If value is
less than key1, use the current
partition.
If value equals key1,
compare value to key2.
If value is greater than key1,
check the next partition range.
For example, if you have a table of 1.2 million customers and want to partition it to improve parallel query performance and simplify maintenance, you can partition it by fiscal quarter and customer ID, and you can archive the data every quarter, as necessary. However, if the table also includes a month and fiscal quarter columns, and since quarters occur every third month, you can use this partitioning scheme:
alter table telco_facts_ptn partition by range (month_key, customer_key) (p1 values <= (3, 1055000) on part_01, p2 values <= (3, 1100000) on part_02, p3 values <= (6, 1055000) on part_03, p4 values <= (6, 1100000) on part_04, p5 values <= (9, 1055000) on part_05, p6 values <= (9, 1100000) on part_06, p7 values <= (12, 1055000) on part_07, p8 values <= (12, 1100000) on part_08)
However, instead of evenly distributing the 1.2 million rows (150,000 rows to each partition), the odd partitions contain 250,000 rows while the even partitions contain only 50,000 rows. For months one and two (January and February), when Adaptive Server compared the data values to the first key in the first partition, it was less than the key (1 < 3 and 2 < 3), so it put all the January and February data into the first partition, regardless of the customer_key value. Only when Adaptive Server entered the March data did it find that the values were equal to key1 (3=3), so it needed to compare customer_key value with key2. As a result, the even partitions only have data in which the month was equal to the partition key and the customer_key value was greater than the customer_key value for the partition key before it.
