14.3 Index row partitioning

If you partition a table, you can also partition and store its index in multiple user RDAREAs.

Organization of this section
(1) Partitioning key index and non-partitioning key index
(2) Index partitioning guidelines
(3) Design considerations
(4) Example of index row partitioning (HiRDB/Single Server)
(5) Example of index row partitioning (HiRDB/Parallel Server)

(1) Partitioning key index and non-partitioning key index

Before designing a row-partitioned index, you need to understand a partitioning key index and a non-partitioning key index.

An index that satisfies a specified condition is a partitioning key index, while an index that does not satisfy a specified condition is a non-partitioning key index. This condition depends on whether the table is a single-column partitioning or multicolumn partitioning table.

Note
A table partitioning condition based on only one column corresponds to single-column partitioning, and a table partitioning condition based on multiple columns corresponds to multicolumn partitioning.
(a) Single-column partitioning

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

Conditions:
  • Single-column index defined for a column for which storage conditions were specified when partitioning the table (partitioning key)
  • Multicolumn index with a component column 1 for which storage conditions were specified when partitioning the table (partitioning key)

Figure 14-1 shows an example of a partitioning key index based on the STOCK table.

Figure 14-1 Partitioning key index (single-column partitioning)

[Figure]

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 partitioning key column PCODE is specified as an index, it becomes a partitioning key index. If any other column is specified as an index, it becomes the non-partitioning key index.
  2. If the partitioning key column PCODE is specified as component column 1 of a multicolumn index, the multicolumn index becomes a partitioning key index.
  3. If the partitioning key column PCODE is specified as a component column other than component column 1, the multicolumn index becomes a non-partitioning key index.
(b) Multicolumn partitioning

An index that satisfies the following condition is a partitioning key index:

Condition:
  • Index created on multiple columns that includes all columns specified for partitioning in the same order, beginning with the partitioning key

Figure 14-2 shows an example of a partitioning key index based on the STOCK table.

Figure 14-2 Partitioning key index (multicolumn partitioning)

[Figure]

Explanation:

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

  1. This multicolumn index becomes a partitioning key index because it specifies all partitioning keys (columns PCODE and PRICE), and the order of these partitioning keys is the same as when the table was defined.
  2. This multicolumn index becomes a partitioning key index because it specifies all partitioning keys (columns PCODE and PRICE), and the order of these partitioning keys is the same as when the table was defined.
  3. This multicolumn index becomes a non-partitioning key index because it specifies all partitioning keys (columns PCODE and PRICE), but the order of these partitioning keys is not the same as when the table was defined.
  4. This multicolumn index becomes a non-partitioning key index because it specifies all partitioning keys (columns PCODE and PRICE), but the order of these partitioning keys is not the same as when the table was defined.

(2) Index partitioning guidelines

Guidelines for index partitioning depend on whether the index is a partitioning key index or a non-partitioning key index, as shown in Table 14-5.

Table 14-5 Index partitioning guidelines

Type of indexHiRDB/Single ServerHiRDB/Parallel Server
Table partitioned by rows within one serverTable partitioned by rows among multiple servers
Partitioning key indexIndex is also row-partitioned according to its row-partitioned table.Index is also row-partitioned according to its row-partitioned table.Index is also row-partitioned according to its row-partitioned table.
Non-partitioning key indexIndex should not be row-partitioned. Row-partitioning the index may result in poor performance during a search using the index.*Index should not be row-partitioned. Row-partitioning the index may result in poor performance during a search using the index.*
* You should not row-partition a non-partitioning key index. Row-partitioning the index may result in poor performance during a search using the index. Specifically, a search using any of the following paths is disabled, adversely affecting the search performance:
  • KEY SCAN MERGE JOIN
  • LIST SCAN MERGE JOIN
  • L-KEY R-LIST MERGE JOIN
  • L-KEY R-SORT MERGE JOIN
  • L-LIST R-KEY MERGE JOIN
  • L-LIST R-SORT MERGE JOIN
  • L-SORT R-KEY MERGE JOIN
  • L-SORT R-LIST MERGE JOIN
For details about these access paths, see the access path display utility (pdvwopt command) in the manual HiRDB Version 8 Command Reference.
However, if there is a large amount of table data, you should consider index row partitioning. Row-partitioning an index enables table storage RDAREAs and index storage RDAREAs to be managed on a one-by-one basis, thereby improving utilities' operability. For example, when the index is not row-partitioned, if you load data in units of RDAREAs or reorganize each RDAREA, you need to create an index in batch mode after data loading or reorganization is completed. If you row-partition the index, there is no need to execute such batch index creation after loading data in units of RDAREAs or reorganizing each RDAREA.
If an index is defined for a matrix-partitioned table, row partitioning is required just as with partitioning keys even if there is a non-partitioning key index.

(3) Design considerations

(4) Example of index row partitioning (HiRDB/Single Server)

Figure 14-3 shows an example of index row partitioning (HiRDB/Single Server).

Figure 14-3 Example of index row partitioning (HiRDB/Single Server)

[Figure]

Explanation:
  • To avoid disk access contention, place the RDAREAs storing the partitioned table on a disk separate from the RDAREAs storing its index.
  • Row-partition the partitioning key index.
  • If performance is more important than operability, do not row-partition the non-partitioning key index.
  • If operability is more important than performance, row-partition the non-partitioning key index.

(5) Example of index row partitioning (HiRDB/Parallel Server)

(a) Partitioning a table within one server

Figure 14-4 shows an example of index row partitioning (within one server).

Figure 14-4 Example of index row partitioning (within one server)

[Figure]

Explanation:
  • To avoid disk access contention, place the RDAREAs storing the partitioned table on a disk separate from the RDAREAs storing its index.
  • Row-partition the partitioning key index.
  • If performance is more important than operability, do not row-partition the non-partitioning key index.
  • If operability is more important than performance, row-partition the non-partitioning key index.
(b) Partitioning a table among multiple servers

Figure 14-5 shows an example of index row partitioning (among multiple servers).

Figure 14-5 Example of index row partitioning (among multiple servers)

[Figure]

Explanation:
  • To avoid disk access contention, place the RDAREAs storing the partitioned table on a disk separate from the RDAREAs storing its index.
  • Row-partition the partitioning key index as well as the non-partitioning key index.