There are two ways to partition a table by rows:
These two methods of table row partitioning are explained as follows.
Key range partitioning divides a table into groups of rows on the basis of ranges of values in a specific column in the table. The column used as the condition for table row partitioning is called the partitioning key.
When this method is used, it is possible to tell which table data is stored in which RDAREA.
Row partitioning can be specified in two ways:
Comparison operators are used to specify conditions for determining which table data is to be stored in each RDAREA. Only one range of storage condition values can be specified for each RDAREA.
Literals are used to specify the boundary values of the data to be stored in each RDAREA. Multiple ranges determined by boundary values can be specified for one RDAREA. Boundary values can also be specified with matrix partitioning. For details about matrix partitioning, see 13.4 Table matrix partitioning.
Hash partitioning uses the values of a table column as a hash function for dividing the table and storing it uniformly in RDAREAs. The column specified for partitioning the table is called the partitioning key. This method is used to distribute the table data uniformly among the RDAREAs without having to deal with key ranges. Hash partitioning can be combined with key range partitioning with boundary values specified to achieve matrix partitioning. For details about matrix partitioning, see 13.4 Table matrix partitioning.
The two types of hash partitioning are flexible hash partitioning and FIX hash partitioning.
When a table is divided and stored in multiple RDAREAs using flexible hash partitioning, there is no way to know which data is stored in which RDAREA. Therefore, in order to search for particular data in the table, all back-end servers containing the table are subject to search processing.
When a table is partitioned using FIX hash partitioning, HiRDB identifies which table data is stored in which RDAREA. Therefore, only the back-end server believed to contain the desired data is subject to search processing.
The key selected as the partitioning key should satisfy the following conditions:
For hash partitioning, either a single column or multiple columns can be selected as the partitioning key. If a single column is specified and there are too few different values in the column for purposes of partitioning or the key values are unevenly distributed, data may not be divided uniformly. In this case, more than one column on which partitioning can be based should be specified in order to distribute the data more uniformly among the RDAREAs.
Twelve hash functions are available for hash partitioning, as listed in Table 13-2.
Table 13-2 Hash functions
Hash function | Explanation |
---|---|
HASH0 | This hash function can be used with data types DATE, TIMESTAMP, CHAR(8) #1or CHAR(6)#1 in the columns specified for partitioning. It uses year and month values to hash data and allocate, by circulating on a monthly basis, the RDAREAs in which the data is stored. You need to specify a single column for partitioning keys. |
HASH1 | This hash function can be used with all data types in the columns specified for partitioning. It hashes all bytes#2 of the data from all columns specified for partitioning. This hash function can be specified for columns whose data length is 0 or more bytes. |
HASH2 | This hash function can be used with all data types in the columns specified for partitioning. It hashes all bytes#2 of the data from all columns specified for partitioning. This hash function can be specified for columns whose data length is 0 or more bytes. You use this hash function when HASH1 is unable to store the data uniformly among the RDAREAs. |
HASH3 | Use this hash function when the data type of the columns to be partitioned is INTEGER or SMALLINT. It hashes the final 2 bytes#2 of all partitioned columns. This hash function can be specified only for columns whose data length is at least 2 bytes. |
HASH4 | Use this hash function when the data type of the columns to be partitioned is DATE. It hashes the initial 4 bytes#2 of all partitioned columns. This hash function can be specified only for columns whose data length is at least 4 bytes. |
HASH5 | Use this hash function when the data type of the columns to be partitioned is TIME. It hashes the initial 3 bytes#2 of all partitioned columns. This hash function can be specified only for columns whose data length is at least 3 bytes. |
HASH6 | This hash function can be used with all data types in the columns specified for partitioning. The most appropriate type is DECIMAL. It hashes all bytes#2 of the data from all columns specified for partitioning. This hash function can be specified for columns whose data length is 0 or more bytes. |
HASHA | This hash function can be used with all data types in the columns specified for partitioning. It hashes all bytes#2 of the data from all columns specified for partitioning. This hash function can be specified for columns whose data length is 0 or more bytes. |
HASHB | This hash function can be used with all data types in the columns specified for partitioning. It hashes all bytes#2 of the data from all columns specified for partitioning. This hash function can be specified for columns whose data length is 0 or more bytes. You use this hash function when HASHA is unable to store the data uniformly among the RDAREAs. |
HASHC | Use this hash function when the data type of the columns to be partitioned is INTEGER or SMALLINT. It hashes the final 2 bytes#2 of all partitioned columns. This hash function can be specified only for columns whose data length is at least 2 bytes. |
HASHD | Use this hash function when the data type of the columns to be partitioned is DATE. It hashes the initial 4 bytes#1 of all partitioned columns. This hash function can be specified only for columns whose data length is at least 4 bytes. |
HASHE | Use this hash function when the data type of the columns to be partitioned is TIME. It hashes the initial 3 bytes#2 of all partitioned columns. This hash function can be specified only for columns whose data length is at least 3 bytes. |
HASHF | This hash function can be used with all data types in the columns specified for partitioning. The most appropriate type is DECIMAL. It hashes all bytes#2 of all data of all partitioned columns. This hash function can be specified for columns whose data length is 0 or more bytes. |
For details about how to create a UAP for using a hash function for table partitioning, see the manual HiRDB Version 8 UAP Development Guide.
Hash functions are used at the following times:
Table 13-3 shows the differences among key range partitioning, flexible hash partitioning, and FIX hash partitioning.
Table 13-3 Differences among key range partitioning, flexible hash partitioning, and FIX hash partitioning
Difference | Key range partitioning | Flexible hash partitioning | FIX hash partitioning |
---|---|---|---|
Database design | Key ranges must be taken into account when database is designed. | There is no need to take key ranges into account when database is designed. | There is no need to take key ranges into account when database is designed. |
Retrieval | Only back-end servers that may contain the applicable data according to the search condition are subject to retrieval processing. | All back-end servers containing the table are subject to retrieval processing. | Only back-end servers that may contain the applicable data according to the search condition are subject to retrieval processing (= and IN predicates only).1 |
Support for increasing the amount of data | If keys increase, data may be concentrated in some RDAREAs. | Data is already stored uniformly in RDAREAs even if data increases. | Data is already stored uniformly in RDAREAs even if data increases. |
Handling of RDAREA shutdown | SQLs can be executed if their search condition is specified so that no shutdown RDAREA is accessed. | If even one of the RDAREAs containing the table subject to retrieval is shut down, SQLs cannot be executed regardless of their search condition. | SQLs can be executed if their search condition is specified so that no shutdown RDAREA is accessed.2 |
Change in number of table partitions | Table must be re-created and reorganized. | ALTER TABLE can be used to add RDAREAs, and reorganization of the table is not required. | Table must be re-created and reorganized. ALTER TABLE can be used to add RDAREAs only if the table contains no data. |
Data loading and reloading in units of RDAREAs | Data is checked to see if it is the correct data to be stored in the corresponding RDAREAs. | Data is not checked to see if it is the correct data to be stored in the corresponding RDAREAs. | Data is checked to see if it is the correct data to be stored in the corresponding RDAREAs. |
Method for creating an input data file by RDAREA during data loading | Input data is classified by RDAREA taking into account the key ranges. | Input data is classified so that the number of data items per RDAREA becomes uniform. | An application is created using a hash function for table partitioning,3 and input data is classified by RDAREA. |
Updating of partitioning key | Updating must use existing values. | Can be updated. | Updating must use existing values. |
UNIQUE definition for cluster key and index definition with UNIQUE specified | UNIQUE cannot be specified. | UNIQUE cannot be specified. | UNIQUE can be specified. |
Changing the partition storage conditions by ALTER TABLE | Partitioning storage conditions can be changed for the following method:
| Partition storage conditions cannot be changed. RDAREAs can be added by ALTER TABLE. | Partition storage conditions cannot be changed. RDAREAs cannot be added by ALTER TABLE. |
When table row partitioning is defined, the following specification rules apply:
Figure 13-4 shows an example of key range partitioning (with storage condition specified).
Figure 13-4 Example of key range partitioning with storage condition specified
Figure 13-5 shows an example of key range partitioning (with boundary value specified).
Figure 13-5 Example of key range partitioning with boundary values specified
Figure 13-6 shows an example of flexible hash partitioning and FIX hash partitioning.
Figure 13-6 Example of flexible hash partitioning and FIX hash partitioning