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 they are actually used during SQL statement execution is determined by the search conditions specified in the WHERE clause, the WHERE search condition of the update SQL statement, 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	In the search condition of the WHERE clause, the WHERE search condition of update SQL statements, or the ON search condition of joined tables, the index constituent column^#1 of the range index must be specified in one of the following predicates.	--
	Comparison predicate However, if the same table column^#2 is specified on both the left and right sides of the comparison operator, the range index is not used.	Example 1 Example 2 Example 3 Example 14 Example 15
	BETWEEN predicate However, if the same table column^#2 as value expression 1 is specified in value expression 2 or value expression 3, the range index is not used.	Example 4 Example 5
	IN predicate (value expression) However, if a column^#2 from the same table as the value expression on the left side of the IN predicate is specified in the value expression on the right side of the IN predicate, the range index is not used.	Example 6 Example 7
	IN predicate (table subquery) The range index is used only when 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
	Logical predicate^#3 The range index is used only when the condition is IS TRUE or IS FALSE.	Example 18
	Quantified predicate The range index is used only when 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 three or more columns^#2 from different tables are specified in the predicate explained in item 1, the range index is not 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 only when a predicate explained in 1 contains an external reference column in either `external-reference-column`=`column-specification` format or `column-specification`=`external-reference-column` format.	Example 13 Example 16

#1

If the column specified in the array value expression of the array element reference (the column with underlining in the following example) is a component column of the range index, it meets the conditions for using the range index.

Example:

"C1"[ANY]=10

"C1"[ANY(1)]=1

"C1"[2]=1

#2

The column specified in the array value expression of the array element reference also applies.

#3: Logical predicates are converted into comparison predicates and NULL predicates by HADB for execution. For details about logical predicates, see Logical predicates in the manual HADB SQL Reference.

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