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)
The following figure shows an index that qualifies as a partitioning key index, using the inventory chart below as an example.
Figure 13-1 Partitioning key index (single-column partitioning)
![[Figure]](figure/zu130010.gif)
- 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
- 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.
- If the partitioning key column PCODE is specified as component column 1 of a multicolumn index, the multicolumn index becomes a partitioning key index.
- 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
The following figure shows an index that qualifies as a partitioning key index, using the inventory chart below as an example.
Figure 13-2 Partitioning key index (multicolumn partitioning)
![[Figure]](figure/zu130020.gif)
- 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
- 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.
- 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.
- 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.
- 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.
Guidelines for index partitioning depend on whether the index is a partitioning key index or a non-partitioning key index, as shown in the following table.
Table 13-5 Index partitioning guidelines
Type of index | HiRDB/Single Server | HiRDB/Parallel Server |
---|
Table partitioned by rows within one server | Table partitioned by rows among multiple servers |
---|
Partitioning key index | Index 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 index | Index 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 9 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.
The following figure shows an example of index row partitioning (for HiRDB/Single Server).
Figure 13-3 Example of index row partitioning (HiRDB/Single Server)
![[Figure]](figure/zu130030.gif)
- 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.
(a) Partitioning a table within one server
The following figure shows an example of index row partitioning (in one server).
Figure 13-4 Example of index row partitioning (within one server)
![[Figure]](figure/zu130040.gif)
- 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
The following figure shows an example of index row partitioning (among multiple servers).
Figure 13-5 Example of index row partitioning (among multiple servers)
![[Figure]](figure/zu130050.gif)
- 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.