5.2 B-tree indexes and text indexes used during execution of SQL statements
You must define B-tree indexes and text indexes that are appropriate for your intended search conditions because the availability of B-tree indexes and text indexes greatly affects performance.
This section explains how to determine the B-tree indexes and text indexes to be used during execution of SQL statements, and how to check the index used during execution of an SQL statement.
In this section, the term index refers to both B-tree indexes and text indexes.
- Notes
-
-
The index selection method explained here is applicable to query expressions obtained after internal derived tables have been expanded or to search conditions that have been converted by equivalent exchange. For details about expansion of internal derived tables, see the topic Internal derived tables in the manual HADB SQL Reference. For details about equivalent exchange for search conditions, see 5.11 Equivalent exchange of search conditions.
-
If, in a value expression specified in the search conditions, only literals are specified in a scalar operation, that scalar operation might be treated as a literal. For details about scalar operations equivalent to literals, see the table Conditions under which value expressions are equivalent to literals under Rules in Specification format and rules for value expressions in the manual HADB SQL Reference.
-
When an index is specified, that index is used, regardless of the B-tree index priority or selection rules described here. For details about index specifications, see Specification format and rules for index specifications in the manual HADB SQL Reference.
-
When joining tables, depending on the table joining method, indexes might not be used when evaluating the join condition. For details about table joining methods, see 5.5 Table joining methods.
-
You cannot define a text index for a column store table.
-
- Important
-
B-tree indexes defined for a column store table are used in the following cases:
-
An index specification is specified.
For details about index specifications, see Specification format and rules for index specifications in the manual HADB SQL Reference.
-
A set function MIN or MAX is specified.#
-
SELECT DISTINCT is specified.#
-
UNION or UNION DISTINCT is specified.#
-
EXCEPT or EXCEPT DISTINCT is specified.#
-
INTERSECT or INTERSECT DISTINCT is specified.#
-
A quantified predicate with =ANY specification is specified.#
-
An IN predicate with a table subquery is specified.#
-
A column store table is specified as the table to be updated by the UPDATE statement.
-
A column store table is specified as the table to be deleted by the DELETE statement.
-
Cost information for a column store table is collected.
- #
-
The B-tree index is used only when the HADB server determines that the B-tree index can be used efficiently.
For details about how to check the B-tree index to be used, see 5.2.5 How to check the index used during execution of an SQL statement.
-