Semantically partitioned tables have one partition key per table or index. For range- or hash-partitioned tables, the partition key can be a composite key with as many as 31 key columns. If a hash-partitioned table has a composite partitioning key, Adaptive Server takes the values in all partitioning key columns and hashes the resultant data stream with a system-supplied hash function.
When a range-partitioned table has more than one partitioning key column, Adaptive Server compares values of corresponding partitioning key columns in each data row with each partition upper and lower bound. Each partition bound is a list of one or more values, one for each partitioning key column.
Adaptive Server compares partitioning key values with bounds in the order specified when the table was first created. If the first key value satisfies the assignment criteria of a partition, the row is assigned to that partition and no other key values are evaluated. If the first key value does not satisfy the assignment criteria, succeeding key values are evaluated until the assignment criteria is satisfied. Thus, Adaptive Server may evaluate as few as one partitioning key value or as many as all keys values to determine a partition assignment.
For example, suppose key1 and key2 are partitioning columns for my_table. The table is made up of three partitions: p1, p2, and p3. The declared upper bounds are (a, b) for p1, (c, d) for p2, and (e, f) for p3.
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
Suppose the roysched table in pubs2 is partitioned by range. The partitioning columns are high range (hirange) and royalty (royalty). There are three partitions: p1, p2, and p3. The upper bounds are (5000, 14) for p1, (10000, 10) for p2, and (100000, 25) for p3.
You can create partitions in the roysched table using alter table:
alter table roysched partition by range (hirange, royalty) (p1 values <= (5000, 14), p2 values <= (10000, 10), p3 values <= (100000, 25))
Adaptive Server partitions the rows in this way:
Rows with these partitioning key values are assigned to p1: (4001, 12), (5000, 12), (5000, 14), (3000, 18).
Rows with these partitioning key values are assigned to p2: (6000, 18), (5000, 15), (5500, 22), (10000, 10), (10000, 9).
Rows with these partitioning key values are assigned to p3: (10000, 22), (80000, 24), (100000, 2), (100000, 16).
Adaptive Server evaluates tables with more than two partitioning key columns in a similar manner.
