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
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.
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. |
-- |
|
||
|
||
|
||
|
||
|
||
|
||
2 |
If a predicate explained in 1 is specified in the condition using the logical operator OR or NOT, no range index is used. |
|
3 |
If a scalar operation is used in a predicate explained in 1, no range index is used. |
|
4 |
If columns from three or more different tables are specified in a predicate explained in 1, no range index is used. |
|
5 |
|
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.
(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.