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.
The effectiveness of partitioning on the basis of partitioning keys formed from multiple columns is as follows:
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:
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.
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) (number-of-boundary-values + 1), is 3
2 = 6 in this example:
Registration date | Store number | |
---|---|---|
100 or below | Above 100 | |
2000 or earlier | USR01 | USR02 |
2001 | USR03 | USR04 |
2002 or later | USR05 | USR06 |
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))
Figure 13-14 provides an example of matrix partitioning.
Figure 13-14 Matrix partitioning example (combination of key range partitioning with boundary values specified)
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) (desired partitions to be obtained by hash function); therefore, 3
3 = 9 RDAREAs are needed for this example.
Registration date | Store number and region code (divided into 3 partitions by hash function) | ||
---|---|---|---|
2002 or earlier | USR01 | USR02 | USR03 |
2003 | USR04 | USR05 | USR06 |
2004 or later | USR07 | USR08 | USR09 |
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))
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)