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.
If you are using the GUI edition of HiRDB SQL Executer, you can also use the Dictionary View window to check the defined indexes. For details about how to use the Dictionary View window, see the HiRDB Version 9 UAP Development Guide.
For details, see Help for HiRDB SQL Executer.
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 9 UAP Development Guide.
  1. 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

  2. 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
  1. 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.
  2. Check the Index Name information to see if an expected index is used.
  3. 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]

Defines an index of T1(C1,C2,C3), T1(C1,C2,C4).

(e) Notes
  1. If there are too many index component columns, the time required to search the index itself increases.
  2. 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.
  3. 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]

Defines an index of T2(C1,C2,C3).

(e) Notes
  1. If there is no limitation on the outer table in the join conditions, a nested loop join usually does not take place.
  2. If limitations are specified for both inner and outer tables, a nest loop join may not take place.
  3. 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 9 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:

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:

(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]

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 configuration and a HiRDB parallel server configuration
  • 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 configuration
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 configuration
  • 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]

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]

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 configuration and a HiRDB parallel server configuration
  • 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 configuration
  • 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 configuration
  • 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:

(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]

Defines an index of T1(C1) that uses the null value as an exception value.

SELECT * FROM T1 WHERE C1 IS NULL ([Figure] index not used)
SELECT * FROM T1 WHERE C1 ='a' ([Figure] index used)

(7) Other