5.3.1 Conditions under which range indexes are used during execution of an SQL statement

If range indexes are defined for a table, whether the range indexes are actually used during execution of an SQL statement is determined by the search conditions specified in the WHERE clause, the ON search conditions for joined tables, and the B-tree indexes or text indexes used during execution of an SQL statement.

This subsection explains the conditions under which range indexes are used for skipping chunks, and the conditions under which they are used for skipping segments.

Notes

The conditions explained here are those, used by range indexes, that are applied to query expressions after expansion of internal derived tables or applied to search conditions converted by an equivalent exchange of search conditions. For details about expanding internal derived tables, see Internal derived tables in the manual HADB SQL Reference. For details about the equivalent exchange of search conditions, see 5.11 Equivalent exchange of search conditions.
If only literals are specified in a scalar operation in the value expression specified in the search conditions, 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 joining tables, range indexes might not be used when evaluating the join condition. This depends on the method used to join the table. For details about table joining methods, see 5.5 Table joining methods.
If subqueries are specified, depending on how they are processed, the range index might not be used. For details about how to process subqueries, see 5.6 How to process subqueries.

Organization of this subsection

(1) Range indexes used for skipping chunks
(2) Range indexes used for skipping segments

(1) Range indexes used for skipping chunks

If all the conditions shown in the following table are satisfied, a range index is used to skip chunks during execution of an SQL statement.

Table 5‒4: Conditions under which range indexes are used
No.	Conditions under which range indexes are used	Example
1	A range-indexed column must be specified in one of the predicates listed below in a search condition specified in the `WHERE` clause or in an `ON` search condition for joined tables.	--
	Comparison predicate Note that no range index is used if the same table's columns are specified in both the left-hand and the right-hand terms of a comparison operator.	Example 1 Example 2 Example 3 Example 14 Example 15
	`BETWEEN` predicate Note that no range index is used if the same table's columns are specified in value expression 1 and value expression 2 or 3.	Example 4 Example 5
	`IN` predicate (value expression) Note that no range index is used if the same table's columns are specified in value expression 1 and value expression 2 or any subsequent value expression.	Example 6 Example 7
	`IN` predicate (table subquery) The range index is used in only a case where hash execution is applied as the subquery processing method.	Example 17
	`LIKE` predicate If `LIKE` is specified, one of the following conditions must be satisfied. Note that if `ESCAPE` is specified in the `LIKE` predicate, the escape characters must be specified in a literal or a dynamic parameter. • Only a user information acquisition function is specified in the pattern character string. • Only a dynamic parameter is specified in the pattern character string. • Only a literal that begins with '`character-string%`' (`%` is a special character) is specified in the pattern character string. • Only a literal that begins with '`character-string_`' (`_` is a special character) is specified in the pattern character string. • Only a literal that does not contain a special character (`%` or `_`) is specified in the pattern character string (complete match). If `NOT` `LIKE` is specified, one of the following conditions must be satisfied: • Only a dynamic parameter is specified in the pattern character string. • Only a literal that begins with '`character-string%`' (`%` is a special character) is specified in the pattern character string.	Example 8 Example 9
	Quantified predicate The range index is used in only a case where hash execution is applied as the subquery processing method.	Example 17
2	If a predicate explained in 1 is specified in the condition using the logical operator `OR` or `NOT`, no range index is used.	Example 10
3	If a scalar operation is used in a predicate explained in 1, no range index is used.	Example 11
4	If columns from three or more different tables are specified in a predicate explained in 1, no range index is used.	Example 12
5	A predicate explained in 1 is specified in a subquery that contains an external reference column and nested loop execution is applied as the subquery processing method. However, if a predicate explained in 1 contains external reference columns, range indexes whose indexed columns are those external reference columns are not used. A predicate explained in 1 is specified in a subquery that contains an external reference column and hash execution is applied as the subquery processing method. The range index is used in only a case where a predicate explained in 1 contains an external reference column in `external-reference-column`=`column-specification` format or `column-specification`=`external-reference-column` format.	Example 13 Example 16

If multiple range indexes that satisfy all the conditions in Table 5‒4: Conditions under which range indexes are used are defined for the table, all range indexes that satisfy all conditions are used.

To determine if a range index can be used for skipping chunks, see Checking a range index (whether it can skip chunks) in the HADB Setup and Operation Guide.

To Page Top

(2) Range indexes used for skipping segments

If all the conditions shown in Table 5‒4: Conditions under which range indexes are used are satisfied, a range index is used to skip segments during execution of an SQL statement. If multiple range indexes that satisfy all the conditions in Table 5‒4: Conditions under which range indexes are used are defined for the table, all range indexes that satisfy all conditions are used.

However, if a B-tree index or text index is used during execution of an SQL statement, segments are not skipped by using a range index.

To Page Top