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
-
If LIMITED is specified for the server-defined operand adb_sql_opt_col_bidx_sel_mode, a B-tree index defined on a column store table without cost information is used in the following cases:
-
An index specification is specified.
For details about index specifications, see Index specification in the manual HADB SQL Reference.
-
The 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.#
-
An UPDATE statement is executed on a table with a B-tree index defined.
-
A DELETE statement is executed on a table with a B-tree index defined.
- #
-
The B-tree index is used only when the HADB server determines that the B-tree index can be used efficiently.
For details about the adb_sql_opt_col_bidx_sel_mode operand, see Operands related to indexes (set format) in Detailed descriptions of the server definition operands of Designing the Server Definition in the manual Hitachi Advanced Database Setup and Operation Guide.
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.
-