Key range partitioning is when ranges of values in a specified column are used as the conditions for row-partitioning the table. The specific column that provides the conditions for row partitioning is called the partitioning key. Key range partitioning is used when the actual RDAREAs in which table data is stored must be known. There are two ways to specify this type of row partitioning:

• Storage condition specification
• Boundary value specification

(a) Storage condition specification

Comparison operators are used to specify conditions for selecting the data to be stored in specific RDAREAs. Only one range of values, as specified by a storage condition, can be set in each RDAREA. Figure 3-9 shows an example of key range partitioning with a storage condition specified.

Figure 3-9 Key range partitioning: Example of storage condition specification

Explanation

The STOCK table is row-partitioned using PCODE as the partitioning key; the results of row-partitioning are stored in RDAREA01 and RDAREA02:

CREATE TABLE STOCK
   (PCODE CHAR(4) NOT NULL,PNAME NCHAR(8),
   COLOR NCHAR(1),PRICE INTEGER,SQUANTITY INTEGER
   )IN ((RDAREA01)PCODE<='353M',(RDAREA02));

(b) Boundary value specification

Boundary values for the data to be stored in each RDAREA are specified with literals in ascending order. Multiple ranges delimited by boundary values can be specified for each RDAREA. Figure 3-10 shows an example of key range partitioning with boundary values specified.

Figure 3-10 Key range partitioning: Example of boundary value specification

Explanation

The STOCK table is row-partitioned using PCODE as the partitioning key; the results of row-partitioning are stored in RDAREA01 and RDAREA02:

CREATE TABLE STOCK
   (PCODE CHAR(4) NOT NULL,PNAME NCHAR(8),
   COLOR NCHAR(1),PRICE INTEGER,SQUANTITY INTEGER
   )PARTITIONED BY PCODE
   IN ((RDAREA01)'302S',(RDAREA02)'591S',(RDAREA01));

Hash partitioning is when a table is row-partitioned by using a hash function to store evenly among the storage RDAREAs the values contained in the columns that make up the table. The specific column that provides the conditions for row-partitioning is called the partitioning key. Hash partitioning is used when it is necessary to store the same amount of table data is each of the RDAREAs. Table 3-4 provides an overview of the two types of hash partitioning.

Table 3-4 Types of hash partitioning

Hash partitioning type	Explanation
Flexible hash partitioning	The RDAREAs in which the table is stored cannot be determined. Therefore, a search process must check all back-end servers that may contain parts of the table.
FIX hash partitioning	HiRDB keeps track of the RDAREAs in which the table is stored. Consequently, a search process has to check only the back-end servers that are expected to contain the table's the data.

Figure 3-11 shows an example of hash partitioning.

Figure 3-11 Example of hash partitioning

Explanation

The STOCK table is row-partitioned using PCODE as the partitioning key; the results of row-partitioning are stored in RDAREA01 and RDAREA02:

Note that the actual RDAREAs in which the data is stored may differ from those in the example.

 
CREATE TABLE STOCK
   (PCODE CHAR(4) NOT NULL,PNAME NCHAR(8),
   COLOR NCHAR(1),PRICE INTEGER,SQUANTITY INTEGER
   ) [FIX]* HASH HASH6 BY PCODE
   IN (RDAREA01,RDAREA02);

^* This is specified for FIX hash partitioning.

The RDAREAs into which a row-partitioned table is stored should be allocated for different disks. If they are allocated on the same disk, contention for access to these RDAREAs can occur, resulting in decreased performance.

The concepts of row partitioning among servers and row partitioning within a server apply to a HiRDB/Parallel Server. Row-partitioning among servers is the mode in which a table is row-partitioned over multiple back-end servers. Row-partitioning within a server is the mode in which a table is row-partitioned within a single back-end server. In the case of a HiRDB/Single Server, row-partitioning is always within the server.

Figure 3-12 shows an example of table row partitioning for a HiRDB/Single Server, and Figure 3-13 shows an example of table row partitioning for a HiRDB/Parallel Server.

Figure 3-12 Example of table row partitioning: HiRDB/Single Server

Figure 3-13 Example of table row partitioning: HiRDB/Parallel Server

Explanation

The table is row-partitioned among back-end servers BES1 to BES4.

To row-partition a table, the elements listed below must be specified in the CREATE TABLE definition SQL:

• Allocation of the table to RDAREAs
• Row-partitioning method (specification of storage conditions, boundary values, or hash partitioning)

For details about the row-partitioning design considerations for improving processing performance, see the HiRDB Version 8 Installation and Design Guide.

When new RDAREAs are added to accommodate an increase in the volume of data in a hash-partitioned table (increase in the number of table partitions), there may be significant differences in the amount of data stored in the existing RDAREAs and in the new RDAREAs. The hash facility for hash row partitioning corrects this sort of imbalance in the amount of data in RDAREAs as a result of increasing the number of table partitions. Figure 3-14 illustrates the hash facility for hash row partitioning. This facility can be applied to both FIX hashing and flexible hashing.

For details about the hash facility for hash row partitioning, see the HiRDB Version 8 System Operation Guide.

Figure 3-14 Hash facility for hash row partitioning

Explanation

1. Because the hash-partitioned table became filled with data, an additional RDAREA for storing table data was provided (the number of table partitions was increased). No data is placed in the new RDAREA, which creates a data volume imbalance.
2. The rebalancing utility (pdrbal command) is executed in order to correct the data volume imbalance.
3. Execution of the rebalancing utility moves and re-allocates data in units of hash groups in a process called table rebalancing. The hash facility for hash row partitioning causes HiRDB to divide data into 1024 groups (called hash groups) based upon the result of hashing the primary key. For each group, an RDAREA segment is allocated, and the data is stored. This reallocation of data is also performed in units of hash groups.

Application criteria

• An increase in the amount of data to be stored is anticipated, so an RDAREA is needed for potential future use.^*
• Because of the large amount of data in the table, it would be prohibitively difficult to re-create the table.

^* Although normally an RDAREA cannot be added to a FIX hash-partitioned table in which data is already stored, you can use the hash facility for hash row partitioning to add an RDAREA.

Operating procedure

Following is a summary of the operating procedure for using the hash facility for hash row partitioning:

1. When a hash partitioned table is defined, use one of the hash functions A to F to define the table as a rebalancing table.
2. Add a table storage RDAREA in order to increase the number of table partitions for the table.
3. Execute the rebalancing utility to rebalance the table.