13.4 Table matrix partitioning

Partitioning a table by a combination of partitioning methods using two of the table columns as the partitioning key is called matrix partitioning. The first column used as the partitioning key is called the first dimension partitioning column, and the second column used as the partitioning key is called the second dimension partitioning column. Matrix partitioning involves key range partitioning with boundary values specified for the first dimension partitioning column and then partitioning the resulting data further by the second dimension partitioning column. The following partitioning methods can be specified for the second dimension partitioning column:

A table that has been matrix partitioned is called a matrix-partitioned table.

In order to matrix partition tables, HiRDB Advanced Partitioning Option is required.

Organization of this section
(1) Effects of table matrix partitioning
(2) Criteria
(3) Specification
(4) Matrix partitioning example

(1) Effects of table matrix partitioning

The effectiveness of partitioning on the basis of partitioning keys formed from multiple columns is as follows:

(2) Criteria

We recommend using key range partitioning with boundary values specified for both partitioning columns when the following conditions are met:

We recommend that you combine key range partitioning with boundary values specified and hash partitioning when the following conditions are met:

(3) Specification

You use the CREATE TABLE definition SQL statement with the PARTITIONED BY MULTIDIM operand to specify the following:

The definition rules are as follows:

For an example definition, see (4) Matrix partitioning example.

(4) Matrix partitioning example

(a) Combination of key range partitioning with boundary values specified

Boundary values are specified for the registration_date and store_number columns of the CUSTOMER_TABLE, and the table is matrix-partitioned by registration_date and store_number. The customer data is stored in user RDAREAs (USR01 to USR06) as shown below. The number of user RDAREAs required for storage, based on the formula (number-of-boundary-values + 1) [Figure] (number-of-boundary-values + 1), is 3 [Figure] 2 = 6 in this example:

Registration dateStore number
100 or belowAbove 100
2000 or earlierUSR01USR02
2001USR03USR04
2002 or laterUSR05USR06

The following is the SQL statement to define this matrix-partitioned table:

CREATE FIX TABLE CUSTOMER_TABLE
 (registration_date DATE, store_number INT, customer_name NCHAR(10))
 PARTITIONED BY MULTIDIM(
 registration_date (('2000-12-31'),
 ('2001-12-31')),                                  1.
 store_number ((100))                              2.
 )IN ((USR01,USR02),(USR03,USR04),(USR05,USR06))

Explanation
  1. Specifies the name of the first dimension partitioning column (name of the first column that is used as the partitioning key) and its list of boundary values.
  2. Specifies the name of the second dimension partitioning column (name of the second column that is used as the partitioning key) and its list of boundary values.

Figure 13-14 provides an example of matrix partitioning.

Figure 13-14 Matrix partitioning example (combination of key range partitioning with boundary values specified)

[Figure]

(b) Combination of key range partitioning with boundary values specified and hash partitioning

This subsection describes an example of applying FIX hash partitioning to a second dimension partitioning column.

This example matrix-partitions the CUSTOMER_TABLE by specifying boundary values for registration_date and using a hash function to partition store_number and region_code into three segments. The customer data is stored in user RDAREAs (USR01 to USR09) as shown below. The number of RDAREAs needed for storage is (number of boundary values + 1) [Figure] (desired partitions to be obtained by hash function); therefore, 3 [Figure] 3 = 9 RDAREAs are needed for this example.

Registration dateStore number and region code (divided into 3 partitions by hash function)
2002 or earlierUSR01USR02USR03
2003USR04USR05USR06
2004 or laterUSR07USR08USR09

The following SQL statement defines the table to be matrix-partitioned:

CREATE FIX TABLE CUSTOMER_TABLE
 (registration_date DATE, store_number INT, region_code INT, customer_name NCHAR(10))
 PARTITIONED BY MULTIDIM
 (registration_date (('2002-12-31'),('2003-12-31')), ...1.
 FIX HASH HASH6 BY store_number, region_code     ...2.
 )IN ((USR01,USR02,USR03),
      (USR04,USR05,USR06),
      (USR07,USR08,USR09))

Explanation:
  1. Specifies the name of the first dimension partitioning column (name of the first column to be used as the partitioning key) and its list of boundary values.
  2. Specifies the name of the second dimension partitioning column (name of the second column that is used as the partitioning key) and the hash function name.

Figure 13-15 shows an example of matrix partitioning.

Figure 13-15 Example of matrix partitioning (combination of key range partitioning with boundary values specified and hash partitioning)

[Figure]

[Figure]