13.2.2 Index creation taking into account optimizing based on cost

If a table has multiple indexes, HiRDB selects for use the index with the lowest access cost based on the search conditions specified for the table retrieval. This index selection process is called optimizing based on cost.

HiRDB takes into account the following factors in estimating access cost:

HiRDB provides better table retrieval performance because it optimizes processing based on cost. Table retrieval performance will not be reduced even when an SQL statement that specifies complicated search conditions is executed.

Organization of this subsection
(1) Index creation criteria taking into account optimizing based on cost

(1) Index creation criteria taking into account optimizing based on cost

Because HiRDB optimizes processing based on cost, the user can create a UAP without having to prioritize the indexes to be used by HiRDB. However, the user should examine beforehand how an index should be created for a table that is to be accessed by UAPs.

To take advantage of optimizing based on cost, an index that is to be used by HiRDB should be created taking into account its priority. Consideration should also be given to the difference between a single-column index and a multicolumn index, the use of multiple indexes, and performance depending on the number of indexes.

The following table lists the order of priority for index usage by HiRDB.

Table 13-2 Order of priority for index usage by HiRDB

PriorityIndex used by HiRDBExample of condition specification for index column (C1)
1
Always used#1
Plug-in index specified for the column in the first argument of an index type plug-in function whose condition is IS TRUE.contains(C1,'...') IS TRUE
Index that contains as its index component columns all the columns in the search condition in a structured repetition predicate.ARRAY(C1,C2)[ANY]
(C1='ABC' and C2=10)
C1 and C2 define a multi-column index.
2Plug-in index specified for the column in the first argument of a plug-in-provided function whose condition is IS TRUEwithin(C1,'...') IS TRUE
3Index with UNIQUE specified for a column that is subject to the = limitation condition.C1=100
4Index for a column subject to the = limitation condition.C1=100
5Index for a column subject to the IS NULL limitation condition#2.C1 IS NULL
6Index for a column specified for a prefix search using a literal (%) in the LIKE or SIMILAR predicate pattern character string.C1 LIKE 'ABC%'
C1 SIMILAR TO 'ABC%'
7Index for a column specified for a prefix search other than the above using a literal in the LIKE or SIMILAR predicate pattern character string.C1 LIKE 'ABC_'
C1 SIMILAR TO 'ABC_'
8Index for a column subject to a limitation condition in the IN predicate.C1 IN(10, 20, 30)
9Index for a column subject to a limitation condition in the BETWEEN predicate.C1 BETWEEN 20 AND 40
Index for a column for which a range condition is specified.20<=C1 AND C1<=40
10Single-column index for a column subject to a limitation condition in the IN predicate using a subquery that has no external reference.C1 IN(SELECT C1 FROM T2)
Single-column index for a column subject to a limitation condition in the =ANY or =SOME quantified predicate using a subquery that has no external reference.C1=ANY(SELECT C1 FROM T2)
C1=SOME(SELECT C1 FROM T2)
11Index for a column subject to the >, >=, <, or <= limitation condition.C1>50
C1<=200
12#3Index for a column specified for a scalar operation (system-defined scalar function, other than IS_USER_CONTAINED_IN_HDS_GROUP)#2.length(C1)=10
13Index for a column subject to a limitation condition in the NOT BETWEEN predicate.C1 NOT BETWEEN 10 AND 30
14Index for a column subject to a limitation condition in the XLIKE predicate, or in LIKE or SIMILAR predicates other than the above.C1 XLIKE '%ABC%'
C1 LIKE '%ABC%'
C1 SIMILAR TO '%ABC%'
15Index for a column specified in an argument of the set function (MIN or MAX)#4.MIN(C1)
MAX(C1)
16Index for a join condition column or in a column subject to grouping or sorting.ORDER BY C1
--Index for a column subject to a negation limitation condition (except NOT BETWEEN).C1 NOT LIKE '%ABC%' C1 IS NOT NULL
Index for a column subject to a limitation condition in the quantified predicate ANY or SOME other than the above.C1>=ANY(SELECT C1 FROM T2)
C1>SOME(SELECT C1 FROM T2)
Index for a column subject to a limitation condition in the quantified predicate ALL.C1>ALL(SELECT C1 FROM T2)
Plug-in index specified for the column in the first argument of a plug-in-provided function whose condition is IS FALSE or IS UNKNOWNwithin(C1,'...') IS FALSE
Legend:
--: Indexes that are not used.
Notes
  1. The contains function call is a function provided by the HiRDB Text Search Plug-in.
  2. The within function call is a function provided by the HiRDB Spatial Search Plug-in.
  3. An index cannot be used if it is for a column subject to a limitation condition that contains a subquery involving external referencing.
  4. If indexes can be used in the conditional expressions on both the terms of the OR operator, the priority depends on the predicate used in the conditional expressions.
  5. A limitation condition refers to a search condition other than the join condition.
  6. HiRDB may not use a defined index if it determines that the index cannot be used effectively.
#1: The index indicated as Always used in the Priority column must be defined; otherwise, an error results.
#2: For the following types of columns, do not create an index whose exception key is the null value:
  • Column for which the IS NULL limitation condition is specified.
  • Column for which a limitation condition includes VALUE and CASE expressions.
  • Column with the BIT_AND_TEST limitation condition for which IS UNKNOWN, IS NOT TRUE, or IS NOT FALSE is specified.
You can create indexes with limitation conditions other than as indicated above. Table 13-3 shows whether HiRDB uses an index whose exception key is the null value.
#3: Only when Key conditions that include a scalar operation is selected as an SQL optimization option does an index have this usage priority. For details about SQL optimization options, see the HiRDB Version 9 UAP Development Guide. Depending on the predicate, an index may have a better priority. If negation is not included, the priority order is in the range of 13-15; if negation is included, the priority order is 13 or up.
#4: In the case of an SQL statement specifying one table without specifying GROUP BY, the index for the column specified in the argument is used if only one set function (MIN or MAX) is specified and one of the following conditions is satisfied:
  • The component column of a single-column index is specified in the set function's argument.
  • The column specified in the set function's argument is component column n of a multicolumn index without an exception key value and = or IS NULL is specified in component columns 1 through n-1.
  • The column specified in the set function's argument is component column n of a multicolumn index with an exception key value and = is specified in component columns 1 through n-1.

    Table 13-3 Whether HiRDB uses an index whose exception key value is the null value

    Limitation condition specified in the component columnWhether index is used
    IS NULL, VALUE, CASE expression, and BIT_AND_TESTOther than IS NULL, VALUE, CASE expression, and BIT_AND_TEST#1
    SpecifiedSpecifiedUsed
    SpecifiedNot specifiedNot used
    Not specifiedSpecifiedUsed#2
    Not specifiedNot specifiedNot used#3
#1: Applicable to the limitation conditions for priority levels 4-15 shown in Table 13-2.
#2: HiRDB may not use the index if it determines that the index cannot be used effectively.
#3: The index is used for retrieval if all the following conditions are satisfied:
  • The selection expression consists of only set functions that use the index component column as the argument.
  • Only one table is specified in the FROM clause.
  • The WHERE clause is not specified.

If indexes are created consistent with the priorities shown in Table 13-2, favorable results can be obtained in narrowing the search conditions specified in the SQL statement. However, an index with a high priority may not be used if HiRDB determines as a result of cost-based optimization that its use would not be effective.