When you have a row-partitioned table, you need to know whether an index being defined is a partitioning key index or a non-partitioning key index. For details about partitioning key indexes and non-partitioning key indexes, see Section (3) Partitioning key index and non-partitioning key index as follows. Table 3-6 shows guidelines for row-partitioning an index by index type.

Table 3-6 Guidelines for row-partitioning an index: HiRDB/Single Server

Index type	Partitioning guidelines
Partitioning key index	Row-partition the index so that it corresponds to the row-partitioned table.
Non-partitioning key index	We recommend that the index not be row-partitioned, because row-partitioning the index can reduce the performance of index-based searches. However, if the table contains an extremely large amount of data, you may wish to consider row-partitioning it. Row-partitioning an index allows the system to manage the table storage RDAREAs and the index storage RDAREAs on a 1-to-1 basis, which will improve the operability of the utilities. For example, if data is loaded by RDAREA, or if RDAREAs are reorganized without the index having been row-partitioned, you will have to batch-create an index after you have loaded the data or reorganized the RDAREAs. If the index is row-partitioned, you will not have to batch-create an index after data loading or RDAREA reorganization.

Figure 3-23 shows an example of row-partitioning an index for a HiRDB/Single Server.

Figure 3-23 Example of row-partitioning an index: HiRDB/Single Server

Explanation

• To prevent disk access contention, the RDAREAs storing the partitioned table and the index are allocated to different disks.
• The partitioning key index is row-partitioned.
• If better performance is important, you would not row-partition non-partitioning key indexes.
• If better operability is important, you would row-partition non-partitioning key indexes.

In the case of a HiRDB/Parallel Server, the index row partitioning guidelines depend on whether the table is partitioned within a server or among servers.

(a) Table is row-partitioned within a server

You need to know whether the index being defined is a partitioning key index or a non-partitioning key index. For details on partitioning key indexes and non-partitioning key indexes, see Section (3) Partitioning key index and non-partitioning key index below. Table 3-7 shows guidelines for row-partitioning an index by index type.

Table 3-7 Guidelines for row-partitioning an index: HiRDB/Parallel Server)

Index type	Partitioning guidelines
Partitioning key index	Row-partition the index so that it corresponds to the row-partitioned table.
Non-partitioning key index	We recommend that the index not be row-partitioned, because row-partitioning the index can reduce the performance of index-based searches. However, if the table contains an extremely large amount of data, you may wish to consider row-partitioning it. Row-partitioning an index allows the system to manage the table storage RDAREAs and the index storage RDAREAs on a 1-to-1 basis, which will improve the operability of the utilities. For example, if data is loaded by RDAREA, or if RDAREAs are reorganized without the index having been row-partitioned, you will have to batch-create an index after you have loaded the data or reorganized the RDAREAs. If the index is row-partitioned, you will not have to batch-create an index after data loading or RDAREA reorganization.

Figure 3-24 shows an example of row-partitioning an index within a server.

Figure 3-24 Example of row-partitioning an index within the server

Explanation

• To prevent disk access contention, allocate the RDAREAs storing the partitioned table and the index to different disks.
• Row-partition a partitioning key index.
• If better performance is important, do not row-partition non-partitioning key indexes.
• If better operability is important, row-partition non-partitioning key indexes.

(b) Table is row-partitioned among servers

You must row-partition the index so that it corresponds to the row-partitioned table. You need not know whether the index being defined is a partitioning key index or a non-partitioning key index. Figure 3-25 shows an example of row-partitioning an index among servers.

Figure 3-25 Example of row-partitioning an index among servers

Explanation

• To prevent disk access contention, the RDAREAs storing the partitioned table and the index are allocated on different disks.
• Both partitioning key indexes and non-partitioning key indexes are row-partitioned.

An index that satisfies a particular condition becomes a partitioning key index. An index that does not satisfy the condition is called a non-partitioning key index. The condition is explained below. The condition is whether the table involved is partitioned on the basis of single-column partitioning or multicolumn partitioning. When only one column is used in the table partitioning condition, the partitioning is said to be single-column partitioning; when multiple columns are used in the table partitioning condition, the partitioning is said to be multicolumn partitioning.

(a) Single-column partitioning

An index satisfying one of the following conditions is a partitioning key index:

Conditions

• Single-column index defined in the column (partitioning key) specifying the storage conditions when the table is row-partitioned.
• Multicolumn index with the column (partitioning key), defined as the first member column, specifying the storage conditions when the table is row-partitioned.

Figure 3-26 shows an example (based on the STOCK table) of an index that becomes a partitioning key index.

Figure 3-26 Partitioning key index: Single-column partitioning

Explanation

CREATE INDEX A12 ON STOCK (PCODE ASC)              1
CREATE INDEX A12 ON STOCK (PCODE ASC,PRICE DESC)   2
CREATE INDEX A12 ON STOCK (PRICE DESC,PCODE ASC)   3

1. If the PCODE column, which is the partitioning key, is used as an index, the index becomes a partitioning key index. If any other column is used as an index, the resulting index becomes a non-partitioning key index.
2. Specifying the PCODE column, which is the partitioning key, as the first constituent column of the index makes the resulting multicolumn index a partitioning key index.
3. Specifying the PCODE column, which is the partitioning key, as a column other than the first constituent column of the index makes the resulting multicolumn index a non-partitioning key index.

(b) Multicolumn partitioning

An index satisfying the following condition is a partitioning key index:

Condition

• The index is created on the basis of multiple columns, beginning with the partitioning key and containing all the columns specified for partitioning from the beginning and without any change in their order.

Figure 3-27 shows an example (based on the STOCK table) of an index that becomes a partitioning key index.

Figure 3-27 Partitioning key index: Multicolumn partitioning

Explanation

CREATE INDEX A12 ON STOCK (SCODE ASC,PRICE DESC)  1
CREATE INDEX A12 ON STOCK
            (SCODE ASC,PRICE DESC,SQUANTITY ASC)  2
CREATE INDEX A12 ON STOCK (PRICE DESC,PCODE ASC)  3
CREATE INDEX A12 ON STOCK
            (SCODE ASC,SQUANTITY DESC,PRICE ASC)  4

1. All partitioning keys (PCODE and PRICE columns) are specified, and these keys are specified in the same order as in the table definition. Therefore, this multicolumn index is a partitioning key index.
2. All partitioning keys (PCODE and PRICE columns) are specified, and these keys are specified in the same order as in the table definition. Therefore, this multicolumn index is a partitioning key index.
3. All partitioning keys (PCODE and PRICE columns) are specified, but these keys are specified in an order that differs from the table definition order. Therefore, this multicolumn index is a non-partitioning key index.
4. All partitioning keys (PCODE and PRICE columns) are specified, but these keys are specified in an order that differs from the table definition order. Therefore, this multicolumn index is a non-partitioning key index.