Composite Partitioning Keys

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, SAP ASE 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, SAP ASE 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.

SAP ASE 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, SAP ASE 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))

SAP ASE 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).

SAP ASE evaluates tables with more than two partitioning key columns in a similar manner.