Hitachi

Hitachi Advanced Database Application Development Guide

5.2.1 Priority and selection rules for indexes

If multiple indexes are defined for a table, HADB determines which index to use based on the search condition specified in the WHERE clause, or the ON search condition specified for a joined table.

This section explains the priority and selection rules for indexes used during execution of an SQL statement.

Organization of this subsection

(1) Index priority

If the predicate specified for a search condition is in any of the formats shown in the following table, an index is used during execution of an SQL statement. Note that only one index is used during execution of an SQL statement. Therefore, if multiple indexes are defined for a table, the index to be used is determined according to the priority shown in the following table.

Table 5‒1: Index priority when multiple indexes are defined for a table

Priority

Condition for index

Example of this priority (C1 is the column for which the index has been defined)

1

Unique index (B-tree index) that specifies all indexed columns in a = condition

"C1"=100

"C1"=100+?

"T1"."C1"=CAST("T2"."C1" AS INTEGER)

2

B-tree index with indexed columns specified in a = condition

"C1"=100

"C1"=100+?

"T1"."C1"=CAST("T2"."C1" AS INTEGER)

3

B-tree index with indexed columns specified in an IS NULL condition

"C1" IS NULL

4

B-tree index with indexed columns specified in a LIKE predicate that satisfies all the following conditions:

  • A literal is specified as the pattern character string

  • The predicate performs a leading-match search that specifies the special character %

Escape characters must be specified as literals.

"C1" LIKE 'ABC%'

"C1" LIKE 'AB\%C%' ESCAPE '\'

5

B-tree index with indexed columns specified in a LIKE predicate that satisfies both of the following conditions:

  • A literal is specified as the pattern character string

  • The predicate performs a leading-match search specified in a way other than that described for priority 4

Escape characters must be specified as literals.

"C1" LIKE 'ABC__'

"C1" LIKE 'ABC%E'

"C1" LIKE 'AB\_C%E' ESCAPE '\'

B-tree index with indexed columns specified in a LIKE predicate that uses complete-match retrieval with a literal specified as the pattern character string.

Escape characters must be specified as literals.

"C1" LIKE 'ABCDE'

"C1" LIKE 'AB\_CDE' ESCAPE '\'

B-tree index with indexed columns specified in a LIKE predicate that specifies a user information acquisition function as the pattern character string.

Escape characters must be specified as literals.

"C1" LIKE CURRENT_USER

"C1" LIKE CURRENT_USER ESCAPE '\'

B-tree index with indexed columns specified in a LIKE predicate in which a dynamic parameter is used as the pattern character string.

Escape characters must be specified as literals.

"C1" LIKE ?

"C1" LIKE ? ESCAPE '\'

B-tree index with indexed columns specified in a LIKE predicate that specifies a dynamic parameter as an escape character.

A literal, user information acquisition function, or dynamic parameter must be specified as the pattern character string.

"C1" LIKE 'AB\%C%' ESCAPE ?

"C1" LIKE CURRENT_USER ESCAPE ?

"C1" LIKE ? ESCAPE ?

6

Text index with indexed columns specified in a CONTAINS scalar function that includes a word-context search specification (in which a notation-correction-search specification or a synonym-search specification is not included)#2, #8

CONTAINS("C1",'WORDCONTEXT("ABC")')>0

7

Text index with indexed columns specified in a CONTAINS scalar function that satisfies either of the following conditions#2, #4, #8:

  • A word-context search specification and notation-correction-search specification are included.

  • A word-context search specification and synonym-search specification are included.

CONTAINS("C1",'WORDCONTEXT(IGNORECASE("ABC"))')>0

CONTAINS("C1",'WORDCONTEXT(SORTCODE("ABC"))')>0

CONTAINS("C1",'WORDCONTEXT(SYNONYM("DIC1","ABC"))')>0

8

Text index with indexed columns specified in a LIKE predicate#1

"C1" LIKE 'ABC'

"C1" LIKE 'ABC%'

"C1" LIKE '%ABC%'

"C1" LIKE '%ABC'

"C1" LIKE ?

Text index with indexed columns specified in a CONTAINS scalar function (in which a notation-correction-search specification, synonym-search specification, or word-context search specification is not included)#2

CONTAINS("C1",'"ABC"')>0

9

Text index that satisfies all of the following conditions#1, #3:

  • OR logical operators are used to specify multiple conditions that specify LIKE predicates.

  • An indexed column is specified as the match value of the LIKE predicate.

"C1" LIKE 'ABC' OR "C1" LIKE 'DEF'

"C1" LIKE 'ABC%' OR "C1" LIKE 'DEF%'

"C1" LIKE '%ABC%' OR "C1" LIKE '%DEF%'

"C1" LIKE ? OR "C1" LIKE ?

Text index with indexed columns specified in a CONTAINS scalar function that includes either of the following specifications#2, #4:

  • Notation-correction-search specification (only when a word-context search specification is not included)

  • Synonym-search specification (only when a word-context search specification is not included)

CONTAINS("C1",'IGNORECASE("ABC")')>0

CONTAINS("C1",'SORTCODE("ABC")')>0

CONTAINS("C1",'SYNONYM("DIC1","ABC")')>0

Text index that satisfies all of the following conditions#1, #2, #3, #4:

  • At least one each of a condition that specifies a LIKE predicate and a condition that specifies the CONTAINS scalar function are specified using the OR logical operator.

  • An indexed column is specified as the match value of the LIKE predicate.

  • An indexed column is specified in the CONTAINS scalar function.

"C1" LIKE '%ABC%'

OR

CONTAINS("C1",'"DEF"')>0

"C1" LIKE '%ABC%'

OR

CONTAINS("C1",'"DEF"')>0

OR

CONTAINS("C1",'IGNORECASE("GHI")')>0

Text index that satisfies all of the following conditions#2, #3, #4:

  • Two or more of a condition that specifies the CONTAINS scalar function are specified using the OR logical operator.

  • An indexed column is specified in the CONTAINS scalar function.

CONTAINS("C1",'"ABC"')>0

OR

CONTAINS("C1",'"DEF"')>0

CONTAINS("C1",'"ABC"')>0

OR

CONTAINS("C1",'IGNORECASE("ABC")')>0

OR

CONTAINS("C1",'SORTCODE("ABC")')>0

CONTAINS("C1",'"ABC"')>0

OR

CONTAINS("C1",'IGNORECASE("DEF")')>0

OR

CONTAINS("C1",'SORTCODE("GHI")')>0

OR

CONTAINS("C1",'SYNONYM("DIC1","JKL")')>0

CONTAINS("C1",'WORDCONTEXT("ABC")')>0

OR

CONTAINS("C1",'WORDCONTEXT("DEF")')>0

10

Text index with indexed columns specified in a match value of a LIKE_REGEX predicate#4, #5

"C1" LIKE_REGEX '^ABC'

"C1" LIKE_REGEX '^ABC' FLAG IGNORECASE

11

Text index that satisfies both of the following conditions#3, #4, #5:

  • Two or more conditions that specify LIKE_REGEX predicates are specified using an OR logical operator.

  • An indexed column is specified in the match values of the LIKE_REGEX predicates.

"C1" LIKE_REGEX '^ABC'

OR

"C1" LIKE_REGEX '^DEF'

Text index that meets all of the following conditions#1, #3, #4, #5:

  • At least one each of a condition that specifies a LIKE predicate and a condition that specifies a LIKE_REGEX predicate are specified using an OR logical operator.

  • An indexed column is specified as a match value of the LIKE predicate.

  • An indexed column is specified as a match value of a LIKE_REGEX predicate.

"C1" LIKE '%ABC%'

OR

"C1" LIKE_REGEX '^DEF'

Text index that meets all of the following conditions#2, #3, #4, #5:

  • At least one each of a condition that specifies a LIKE_REGEX predicate and a condition that specifies a CONTAINS scalar function are specified using an OR logical operator.

  • An indexed column is specified in a match value of a LIKE_REGEX predicate.

  • An indexed column is specified in a CONTAINS scalar function.

"C1" LIKE_REGEX '^ABC'

OR

CONTAINS("C1",'"XYZ"')>0

Text index that meets all of the following conditions#1, #2, #3, #4, #5:

  • Multiple conditions that specify a LIKE predicate, a LIKE_REGEX predicate, and a CONTAINS scalar function are specified using OR logical operators.

  • An indexed column is specified as the match value of a LIKE predicate.

  • An indexed column is specified in a match value of a LIKE_REGEX predicate.

  • An indexed column is specified in a CONTAINS scalar function.

"C1" LIKE_REGEX '^ABC'

OR

"C1" LIKE '%DEF%'

OR

CONTAINS("C1",'"XYZ"')>0

12

B-tree index with indexed columns specified in an IN predicate whose comparison values are value specifications only

"C1" IN (10,20,30)

B-tree index with indexed columns specified in an IN predicate whose comparison values are only value specifications that include scalar operations

"C1" IN (10,20,30+?)

"T1"."C1" IN (CASE WHEN 100=? THEN 10 ELSE 20 END,30,40)

13

B-tree index with indexed columns specified in a BETWEEN predicate

"C1" BETWEEN 20 AND 40

"C1" BETWEEN 20 AND 40+?

"T1"."C1" BETWEEN "T2"."C1"-6 MONTH AND "T2"."C1"

B-tree index with indexed columns specified in a range condition that combines two comparison predicates

"C1">=20 AND "C1"<=40

14

B-tree index with indexed columns specified in an IN predicate that satisfies the following condition:

  • A subquery is specified whose comparison values do not include an external reference column.

"C1" IN (SELECT "C1" FROM "T2")

B-tree index with indexed columns specified in an =ANY condition that specifies a subquery that does not include an external reference column

"C1"=ANY(SELECT "C1" FROM "T2")

B-tree index with indexed columns specified in a =SOME condition that specifies a subquery that does not include an external reference column

"C1"=SOME(SELECT "C1" FROM "T2")

15

B-tree index with indexed columns specified in a >, >=, <, or <= condition

"C1">50

"C1"<=200

"C1">=50+?

"T1"."C1" <"T2"."C1"||'X'

16

B-tree index with indexed columns specified in an IN predicate whose comparison values include a column specification

"T1"."C1" IN (10,"T2"."C1")

"T1"."C1" IN (10,"T2"."C1"+?,50)

17

B-tree index with indexed columns specified in a condition that specifies an OR logical operator#6

"C1"<20 OR "C1">40

18

B-tree index with indexed columns specified in a LIKE predicate that satisfies both of the following conditions#7:

  • A literal is specified in the pattern character string.

  • The search is not a leading-match search.

"C1" LIKE '%BCD%'

"C1" LIKE '%B\_CD%' ESCAPE '\'

B-tree index with indexed columns specified in a LIKE predicate that satisfies the following condition:

  • The pattern character string specifies a value expression that contains a column specification.

"T1"."C1" LIKE "T2"."C2" || '%'

B-tree index with indexed columns specified in a LIKE predicate that satisfies the following condition:

  • The pattern character string specifies a value expression that contains a scalar operation.

"C1" LIKE CURRENT_USER || '%'

B-tree index with indexed columns specified in a LIKE predicate that satisfies the following condition:

  • A value expression that specifies a column specification is specified in an escape character.

"T1"."C1" LIKE 'A\%B@_C%'

ESCAPE "T2"."C1"

B-tree index with indexed columns specified in a LIKE predicate that satisfies the following condition:

  • A value expression that includes a scalar operation is specified in an escape character.

"C1" LIKE 'A\%B@_C%' ESCAPE CASE WHEN 10=? THEN '\' ELSE '@' END
Notes

  • For a multiple-column index, the priority shown in this table is applied sequentially starting from the first B-tree indexed column.

  • Basically, the index to be used is determined by the priority shown above. However, depending on the specified search conditions, the priority shown above might not result in an effective evaluation. In this case, a index that does not follow the above priority might be used. If you want to check the index that was actually used for a retrieval, see 5.2.5 How to check the index used during execution of an SQL statement.

#1

This condition does not apply when the LIKE predicate specifies a pattern character string in the following formats:

  • The pattern character string consists only of the special character %.

    Example: "C1" LIKE '%'

  • The pattern character string is an empty string.

    Example: "C1" LIKE ''

  • The pattern character string does not specify two or more consecutive non-special characters.

    Example: "C1" LIKE '%A%', "C1" LIKE '%A%B%'

  • The pattern character string specifies a column in the same table as the column in the match value.

    Example: "T1"."C1" LIKE "T1"."C2"

#2

This condition does not apply when the CONTAINS scalar function specifies a search character string in the following formats. It does apply if a synonym-search specification is not specified, or one is specified but its synonym does not exist in relation to the search character string.

  • The search character string is an empty string.

    Example 1: CONTAINS("C1",'""')>0

    Example 2: CONTAINS("C1",'IGNORECASE("")')>0

    Example 3: CONTAINS("C1",'SORTCODE("")')>0

    Example 4: CONTAINS("C1",'WORDCONTEXT("")')>0

    Example 5: CONTAINS("C1",'WORDCONTEXT_PREFIX("")')>0

  • The search character string consists of a single character.

    Example 1: CONTAINS("C1",'"A"')>0

    Example 2: CONTAINS("C1",'IGNORECASE("A")')>0

    Example 3: CONTAINS("C1",'SORTCODE("A")')>0

    Example 4: CONTAINS("C1",'WORDCONTEXT("A")')>0

    Example 5: CONTAINS("C1",'WORDCONTEXT_PREFIX("A")')>0

    This condition also does not apply when a synonym-search specification is specified in a CONTAINS scalar function and its synonym exists in relation to the search character string, if the synonym is a single character such as A.

  • A word-context search specification is included in a CONTAINS scalar function, and the number of characters in the search character string after elimination of the following symbols is no more than 1:

    • Half-width space (0x20), tab (0x09), line break (0x0A), return (0x0D), period, question mark, exclamation mark, and other single-byte symbols (0x21 to 0x2F, 0x3A to 0x40, 0x5B to 0x60, and 0x7B to 0x7E)

    Example 1: CONTAINS("C1",'WORDCONTEXT("###A")')>0

    Example 2: CONTAINS("C1",'WORDCONTEXT_PREFIX("###A")')>0

#3

Every condition included in the scope of the OR logical operator must be one that uses a LIKE predicate, a LIKE_REGEX predicate, or a CONTAINS scalar function. All columns in the OR logical operator must be indexed columns of the text index subject to selection.

#4

In either of the following circumstances, only text indexes defined with CORRECTIONRULE (notation-correction-search text-index specification) specified in a CREATE INDEX statement are subject to selection.

  • A LIKE_REGEX predicate is specified that specifies IGNORECASE (or I) for FLAG.

  • A CONTAINS scalar function is specified that specifies a notation-correction-search specification.

#5

This condition does not apply when the regular expression character string of the LIKE_REGEX predicate consists of 1 or fewer characters.

#6

All the columns included in the OR logical operator must be indexed columns of the B-tree index subject to selection. The index priority order might change depending on the number of predicates and the format of the condition specified in the OR logical operator.

#7

This condition does not apply if the data type of an indexed column for which a LIKE condition is specified is a variable-length character string data type and a pattern character string in one of the following formats:

  • The special character % (percent sign) is not specified at the end.

    Example 1: "C1" LIKE '%BCD'

    Example 2: "C1" LIKE '%BCD_'

  • The special character % (percent sign) specified at the end is preceded by a single-byte space or the special character _ (underscore).

    Example 1: "C1" LIKE '%BCDΔ%'

    Example 2: "C1" LIKE '%BCD_%'

    Legend: ∆: Half-width space

#8

Only a text index for a word-context search can be selected as the index to be used during a search.

To Page Top

(2) Selection rules for indexes

The index priority described in (1) Index priority is not the only factor that determines which index is used during execution of an SQL statement. The conditions described here are just one of the factors that determine which index is selected.

(a) Selection rules for B-tree indexes

The following table describes the selection rules for B-tree indexes.

Note that selection rules are applied sequentially in ascending order. That is, if selection rule No. 1 does not determine which index to use, selection rule No. 2 is applied next.

Table 5‒2: Selection rules for B-tree indexes

Selection rule

Method of condition specification

1

An = condition is specified for all columns of a unique index.

2

The index with a = join condition contained in consecutive = conditions at the beginning of a search condition is prioritized.

3

If a search condition is specified for the first B-tree indexed column of the indexes, selection is based on the priority of that search condition.

4

The index with more search conditions is prioritized.

5

The index with more key conditions is prioritized.

6

The index with fewer indexed columns is prioritized. For indexes that satisfy selection rule 1, the index with more indexed columns is prioritized.

7

An index that uses a condition specified previously in an SQL statement as a search condition is prioritized.

8

An index that does not create a work table is prioritized.

9

A unique index is prioritized. For indexes for which no search condition is specified, the non-unique index is prioritized.

10

The index with the shorter key length is prioritized.

11

If none of selection rules from 1 to 10 is applied, the B-tree index is selected depending on internal processing.

For details about search conditions and key conditions, see 5.4.1 Evaluation method when B-tree indexes are used.

(b) Selection rules for text indexes

The following table describes the selection rules for text indexes.

Note that selection rules are applied sequentially in ascending order. That is, if selection rule No. 1 does not determine the index to use, selection rule No. 2 is tested next.

Table 5‒3: Selection rules for text indexes

Selection rule

Method of condition specification

1

  • A text index that can evaluate more conditions that specify LIKE predicates is prioritized.

  • A text index that can evaluate more conditions that specify LIKE_REGEX predicates is prioritized.

  • A text index that can evaluate more conditions that specify the scalar function CONTAINS is prioritized.

2

  • A text index with the longer pattern character string for LIKE predicates is prioritized.

  • A text index with the longer regular expression character string for LIKE_REGEX predicates is prioritized.

  • A text index with the longer search character string for the scalar function CONTAINS is prioritized.

3

A text index with the shorter indexed column definition is prioritized.

4

  • A text index that evaluates fewer LIKE predicates, LIKE_REGEX predicates, and CONTAINS scalar functions specified in OR conditions is prioritized.

  • If CONTAINS scalar functions that specify synonym-search specification are specified, the number of synonyms for the search string is added to the number of such scalar functions and compared.

5

If none of selection rules 1 to 4 result in an index being selected, internal processing determines the text index that is selected.

(c) Notes

  • If HADB determines that it cannot use the index effectively based on cost information or other resources, these selection rules might be set aside in favor of a different approach.

  • If HADB determines that it cannot use the index effectively for such reasons as only not predicates being specified in the search conditions for a WHERE clause or the ON search condition for a joined table, the index might not be used.

To Page Top

All Rights Reserved. Copyright (C) 2012, 2020, Hitachi, Ltd.