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 High Availability 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:
In the following cases, we recommend duplicative specification of the RDAREA name specified in hash partitioning when key range partitioning with boundary values is combined with hash partitioning. Specifying the same RDAREA more than once can reduce the number of RDAREAs actually used while keeping the number of partitions unchanged. The amount of data stored in each RDAREA can also be kept even. For details about duplicative specification of RDAREA names, see Figure 12-4 Example of duplicating a table storage RDAREA specification using hash partitioning.
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) x (number-of-boundary-values + 1), is 3 x 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))
The following figure shows an example of matrix partitioning.
Figure 12-15 Example of matrix partitioning (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) x (desired partitions to be obtained by hash function); therefore, 3 x 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))
The following figure shows another example of matrix partitioning.
Figure 12-16 Example of matrix partitioning (combination of key range partitioning with boundary values specified and hash partitioning)