17.6.2 Notes about index definitions
Use the interactive SQL execution utility (HiRDB SQL Executer) to check the index definition information.
- HiRDB SQL Executer available
- To obtain a list of defined indexes, use HiRDB SQL Executer's INDEXES command. To obtain the names of index component columns from the index name, use the INDEXCLM command. For details, see HiRDB SQL Executer's README.
- HiRDB SQL Executer not available
- If HiRDB SQL Executer is not available, obtain the information by searching the data dictionary tables. Examples of searching the data dictionary tables are shown as follows. For details about the data dictionary tables, see the HiRDB Version 8 UAP Development Guide.
- To obtain a list of defined indexes, retrieve SQL_INDEXES from the data dictionary tables:
SELECT INDEX_ID , INDEX_NAME , TABLE_NAME ,
UNIQUE_TYPE , VALUE(ARRAY_TYPE,' ') , COLUMN_COUNT
FROM MASTER.SQL_INDEXES
WHERE TABLE_SCHEMA LIKE USER
ORDER BY 1
- To obtain index component columns from the index name, execute join retrieval of SQL_INDEX_COLINF and SQL_COLUMNS on the data dictionary tables:
SELECT Y.INDEX_ORDER , X.COLUMN_ID , X.COLUMN_NAME ,
VALUE(X.MAX_ELM,1) , Y.ASC_DESC
FROM MASTER.SQL_COLUMNS X , MASTER.SQL_INDEX_COLINF Y
WHERE X.TABLE_SCHEMA = Y.TABLE_SCHEMA
AND X.TABLE_NAME = Y.TABLE_NAME
AND X.COLUMN_NAME = Y.COLUMN_NAME
AND Y.INDEX_NAME LIKE 'IDX1'
AND Y.TABLE_SCHEMA LIKE USER
ORDER BY 1
- Organization of this subsection
- (1) Index definitions
- (2) Index definitions for a table used for join retrieval
- (3) Index used for outer join
- (4) Index definitions for a table used for retrieval specifying ORDER BY or GROUP BY
- (5) Index definitions for a table being retrieved specifying set functions MIN and MAX
- (6) Index definition using the null value as an exception value
- (7) Other
(1) Index definitions
(a) Checking the display
- Check the Scan Type information. If the performance is poor with TABLE SCAN or AND PLURAL INDEXES SCAN, consider defining a multicolumn index that includes all columns for which the index is defined.
- Check the Index Name information to see if an expected index is used.
- Check to see if the range of index is narrowed efficiently by the search condition and key condition. Following are some examples in which the index search range is not narrowed efficiently:
- There is no search condition (SearchCnd:NONE(FULL SCAN) is displayed for the search condition).
- The range of the first component column narrowed by the search condition is from MIN to MAX (FULL SCAN) is displayed after the narrowed range of search condition).
- A wide range of index is being searched.
(b) Better method
If a table is subject to frequent retrieval processing specifying multiple predicates using AND, define a multicolumn index consisting of the predicate columns. In this case, specify the columns that are frequently combined with the = predicate as the index component columns in the ascending order of duplicate values contained in the columns.
(c) Reason
The search time is reduced because the index search range is narrowed.
(d) Example
SELECT * FROM T1 WHERE C1=10 AND C2=30 AND C3 BETWEEN 10 AND 20
SELECT * FROM T1 WHERE C1=10 AND C2=30 AND C3 <= 15
SELECT * FROM T1 WHERE C1=20 AND C2=40 AND C4 = 60
![[Figure]](figure/zueng011.gif)
Defines an index of T1(C1,C2,C3), T1(C1,C2,C4).
(e) Notes
- If there are too many index component columns, the time required to search the index itself increases.
- If a column is updated, maintenance occurs on the index. If a column is updated frequently, you should not include this column in the index component columns.
- Try to minimize the number of indexes to be defined.
(2) Index definitions for a table used for join retrieval
(a) Checking the display
If the Join Type is sort merge join and the performance is poor because a large amount of data is sorted, but the table retrieval condition can be narrowed efficiently, you can achieve effective retrieval by creating an index for the inner table's joined columns and using a nested loop join.
(b) Better method
For a table used for join retrieval with another table with a search range that is narrowed by limitations, define an index with a first component column of the joined column.
If multiple join conditions (n) are specified frequently, define an index that consists of the joined columns as its component columns 1 through n. If there are search conditions other than the join conditions, specify those search conditions following the joined columns (after component columns n + 1) to define the index.
(c) Reason
Because a nested loop join is executed on the outer table for which limitations are specified and the inner table, index for which is defined for the joined column, the utility can use the index for a join operation, thereby reducing the number of input/output operations.
(d) Example
SELECT * FROM T1,T2
WHERE T1.C3=20 AND T1.C1=T2.C1 AND T1.C2=T2.C2 AND T2.C3>10
JOINED COLUMN OF INNER TABLE IS T2 (C1, C2)
![[Figure]](figure/zueng011.gif)
Defines an index of T2(C1,C2,C3).
(e) Notes
- If there is no limitation on the outer table in the join conditions, a nested loop join usually does not take place.
- If limitations are specified for both inner and outer tables, a nest loop join may not take place.
- To use a nested loop join preferentially or forcibly, specify SQL optimization option forced nest-loop-join or prioritized nest-loop-join. For details, see the HiRDB Version 8 UAP Development Guide.
(3) Index used for outer join
(a) Checking the display
Check to see if an expected index is used for outer join (LEFT OUTER JOIN).
(b) Better method
None of the following indexes is used for outer join:
- Index for the outer table's column that is specified for the ON retrieval condition in the FROM clause
- Index for the inner table's column that is specified in the WHERE clause
To execute retrieval using an index, evaluate whether the conditions for the outer table can be specified in the WHERE clause and the conditions for the inner table in the ON retrieval condition.
- Note
- The following shows an outer table and an inner table for outer join:
outer-table LEFT OUTER JOIN inner-table ON ...
WHERE ...
(c) Reason
The utility retrieves all columns from the outer table whether or not the ON retrieval condition in the FROM clause is true. Therefore, the limitation on the outer table's columns specified in the ON retrieval condition in the FROM clause are not subject to the narrowing of range using indexes.
For the inner table's columns, the utility evaluates the ON retrieval condition in the FROM clause, fills the null value, then evaluates the condition in the WHERE clause. Therefore, the limitation on the inner table's columns specified in the WHERE clause is not subject to the narrowing of range using indexes.
(d) Example
SELECT * FROM T1 LEFT JOIN T2 ON T1.C1=T2.C1 and T1.C2='a'
and T2.C2='b'
WHERE T1.C3='c' and T2.C4='d'
The underlined columns are evaluated using the index.
(4) Index definitions for a table used for retrieval specifying ORDER BY or GROUP BY
(a) Checking the display
If executing a sort operation for ORDER BY processing to retrieve a single table, you can eliminate the sort operation by modifying the index definitions in such a manner that the existing index's ascending or descending order is used to sort the ORDER BY column.
(b) Better method
If you are retrieving a single table for which ORDER BY or GROUP BY is specified after narrowing the range by the = predicate (column-specification=value-specification) or IS NULL predicate (column-specification IS NULL), define an index that meets the following two conditions:
- The column specification in the = or IS NULL predicate includes component columns 1 through n consecutively.
- The columns specified in GROUP BY or ORDER BY consecutively follow the component column n + 1.
(c) Reason
The CPU time and input/output time decrease because the range of data to be accessed is narrowed by using an index, and the sort operation can be omitted.
(d) Example
SELECT C2, C3 FROM T1
WHERE C1=10 AND C2= ? AND C3>10
ORDER BY C4 DESC,C5 ASC
![[Figure]](figure/zueng011.gif)
Defines an index of T1(C1 ASC,C2 ASC,C4 DESC,C5 ASC) or T1(C1 ASC,C2 ASC,C4 DESC,C5 ASC,C3 ASC).
(e) Notes
Sort operation cannot be eliminated if any of the following conditions is met:
- Limitations common to both a HiRDB/Single Server and a HiRDB/Parallel Server
- DISTINCT is specified.
- Specified retrieval condition selects a retrieval method that does not use an index.
- T1 is partitioned and a non-partitioning key index is partitioned within the same server (index with an Index Name attribute of d). This does not apply when column=value-specification is specified for all partitioning keys.
- Limitations to a HiRDB/Single Server
- The sort operation cannot be eliminated if T1 is partitioned and the partitioning keys specified in CREATE TABLE are included in the same order of the index component columns from the top (index with an Index Name attribute of d). This does not apply when column=value-specification is specified for all partitioning keys.
- Limitations to a HiRDB/Parallel Server
- SQL executes the INSERT or SELECT statement.
- Specification is made within a subquery.
- A set operation is specified.
- The HAVING clause is specified.
- FOR UPDATE or FOR READ ONLY is specified.
- The sort operation cannot be eliminated if T1 is partitioned, the partitioning keys specified in CREATE TABLE are included in the same order of the index component columns from the top (index with an Index Name attribute of d), and multiple RDAREAs at the same back-end server are allocated. This does not apply when column=value-specification is specified for all partitioning keys.
(5) Index definitions for a table being retrieved specifying set functions MIN and MAX
(a) Checking the display
If GROUP BY is not specified and the performance is poor during retrieval specifying set functions MIN(column) or MAX(column) in a selection expression, define an index in the column specification of the set function, so that the value of the set function can be obtained simply by referencing the minimum or maximum index value. In this case, Group by Mode is IMPLICIT MIN-MAX INDEX.
(b) Better method
There are two better methods:
- Retrieval condition not specified
- For a table used for retrieval specifying at least one of the set functions MIN or MAX, define an index that specifies MIN or MAX for its first component column.
- = predicate (or IS NULL predicate) specified as retrieval condition
- For a table used for retrieval specifying set function MIN or MAX after narrowing the range by the retrieval conditions (n conditions) specified in the = predicate (or IS NULL predicate), define an index that meets all the following conditions:
- The = predicate consecutively specifies component columns 1 through n.
- The column specified with MIN or MAX is component column n + 1.
- A column specified in the other retrieval condition is component column n + 2 or a subsequent column.
(c) Reason
The CPU time and input/output time decrease because the result is obtained by referencing the minimum or maximum index value for the MIN or MAX set function, respectively.
(d) Example
- Retrieval condition not specified
SELECT MIN(C1), MAX(C1) FROM T1
![[Figure]](figure/zueng011.gif)
Defines an index of T1(C1).
- Retrieval condition specified with the = predicate (or IS NULL predicate)
SELECT MIN(C1), MAX(C1) FROM T1 WHERE C2=10 AND C3=20 AND C4<30
![[Figure]](figure/zueng011.gif)
Defines an index of T1(C2,C3,C1,C4).
(e) Notes
The index is not treated as IMPLICIT MIN-MAX INDEX if any of the following conditions is met:
- Limitations common to both a HiRDB/Single Server and a HiRDB/Parallel Server
- A set function other than MIN or MAX is specified.
- When there are multiple MINs or MAXs, columns in the set function arguments do not match.
- The GROUP BY clause is specified.
- The search condition contains a system-defined scalar function, function call, or IS_USER_CONTAINED_IN_HDS_GROUP.
- The search condition contains a column that is not an index component column.
- If FLAT is specified as a argument of the MAX or MIN set function, the search condition contains a repetition column.
- There is no index applicable to section (b), previously.
- T1 is partitioned and retrieval uses a non-partitioning key index that is partitioned within the same server (index with an Index Name attribute of d). This does not apply when column=value-specification is specified for all partitioning keys.
- Limitations to a HiRDB/Single Server
- The sort operation cannot be eliminated if T1 is partitioned and the partitioning keys specified in CREATE TABLE are included in the same order of the index component columns from the top (index with an Index Name attribute of d). This does not apply when column=value-specification is specified for all partitioning keys.
- When T1 is partitioned, retrieval is executed using the index that includes the partitioning keys specified in CREATE TABLE in the same order of the index component columns from the top (index with an Index Name attribute of d). This does not apply when column=value-specification is specified for all partitioning keys.
- Limitations to a HiRDB/Parallel Server
- A set operation is specified.
- SQL executes the INSERT or SELECT statement.
- The HAVING clause is specified.
- FOR READ ONLY is specified.
- When T1 is partitioned, retrieval is executed using the index that includes the partitioning keys specified in CREATE TABLE in the same order of the index component columns from the top (index with an Index Name attribute of d), and multiple RDAREAs at the same back-end server are allocated. This does not apply when column=value-specification is specified for all partitioning keys.
(6) Index definition using the null value as an exception value
(a) Checking the display
Check to see if any of the following is true:
- A retrieval with search condition type IS NULL is executed on an index component column that has many duplicated null values, resulting in poor performance.
- Insertion of the null value, updating to the null value, updating from the null value to another value, or deletion of a null-value row takes place, resulting in poor performance.
(b) Better method
Define an index that uses the null value as an exception value.
(c) Reason
If an index is defined that uses the null value as an exception value, the utility creates the index excluding the null value. If IS NULL is specified in the retrieval condition, the utility uses TABLE SCAN without using the index. However, if a predicate other than IS NULL is used (such as = or between), the utility uses the index.
If there are many occurrences of null value and the index is used to access data pages at random, you can achieve high-speed retrieval by defining an index that uses the null value as an exception value and using TABLE SCAN.
You can reduce the overhead of index maintenance as well as the amount of log information by excluding the null value from the index key values.
(d) Example
SELECT * FROM T1 WHERE C1 IS NULL
![[Figure]](figure/zueng011.gif)
Defines an index of T1(C1) that uses the null value as an exception value.
SELECT * FROM T1 WHERE C1 IS NULL (
index not used)
SELECT * FROM T1 WHERE C1 ='a' (
index used)
(7) Other
- If a lock release wait occurs frequently on a table with a retrieval method of TABLE SCAN, you may be able to reduce the occurrences of lock release wait by defining an index for a column specified in the retrieval condition because the range of data to be accessed can be narrowed.
- If a lock release wait occurs frequently during retrieval using an index, evaluate whether or not you can specify a "search using condition evaluation with no lock" that locks only those rows or key values satisfying the retrieval condition. For details about the search using condition evaluation with no lock, see the HiRDB Version 8 UAP Development Guide.
- If the retrieval method is AND PLURAL INDEXES SCAN, if multiple users update the same table at the same time, deadlock may result. In this case, consider changing the pd_work_table_option operand in the system definitions. For details about the pd_work_table_option operand, see the HiRDB Version 8 System Definition.
- If the access path display utility finds an unused index, you should delete the index. This unneeded index may require extra workload during update processing or extra space in the database.