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.
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
Priority | Index used by HiRDB | Example 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. | |
2 | Plug-in index specified for the column in the first argument of a plug-in-provided function whose condition is IS TRUE | within(C1,'...') IS TRUE |
3 | Index with UNIQUE specified for a column that is subject to the = limitation condition. | C1=100 |
4 | Index for a column subject to the = limitation condition. | C1=100 |
5 | Index for a column subject to the IS NULL limitation condition#2. | C1 IS NULL |
6 | Index 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%' |
7 | Index 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_' |
8 | Index for a column subject to a limitation condition in the IN predicate. | C1 IN(10, 20, 30) |
9 | Index 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 | |
10 | Single-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) | |
11 | Index for a column subject to the >, >=, <, or <= limitation condition. | C1>50 C1<=200 |
12#3 | Index for a column specified for a scalar operation (system-defined scalar function, other than IS_USER_CONTAINED_IN_HDS_GROUP)#2. | length(C1)=10 |
13 | Index for a column subject to a limitation condition in the NOT BETWEEN predicate. | C1 NOT BETWEEN 10 AND 30 |
14 | Index 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%' |
15 | Index for a column specified in an argument of the set function (MIN or MAX)#4. | MIN(C1) MAX(C1) |
16 | Index 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 UNKNOWN | within(C1,'...') IS FALSE |
Table 13-3 Whether HiRDB uses an index whose exception key value is the null value
Limitation condition specified in the component column | Whether index is used | |
---|---|---|
IS NULL, VALUE, CASE expression, and BIT_AND_TEST | Other than IS NULL, VALUE, CASE expression, and BIT_AND_TEST#1 | |
Specified | Specified | Used |
Specified | Not specified | Not used |
Not specified | Specified | Used#2 |
Not specified | Not specified | Not used#3 |
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.