5.3.2 Examples of range indexes used during retrieval
- Organization of this subsection
(1) Example 1
- Definition of range indexes
-
CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE
- Example SQL statement
-
SELECT * FROM "T1" WHERE "C1">10
- Explanation:
-
In this example, range index RIDX1 is used to skip chunks and segments that do not contain data that satisfies the conditions.
(2) Example 2
- Definition of range indexes
-
CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE CREATE INDEX "RIDX2" ON "T1"("C2") IN "DBAREA01" EMPTY INDEXTYPE RANGE CREATE INDEX "RIDX3" ON "T1"("C3") IN "DBAREA01" EMPTY INDEXTYPE RANGE
- Example SQL statement
-
SELECT * FROM "T1" WHERE "C1"=10 AND "C2">20 AND "C3">30
- Explanation:
-
In this example, range indexes RIDX1, RIDX2, and RIDX3 are used to skip chunks and segments that do not contain data that satisfies the conditions.
(3) Example 3 (range indexes are not used)
- Definition of range indexes
-
CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE
- Example SQL statement
-
SELECT * FROM "T1" WHERE "C1">"C2"
- Explanation:
-
When columns from the same table are specified in the right-hand and left-hand terms of a comparison operator, a range index is not used (chunks and segments that do not contain data that satisfies the conditions are not skipped). In this example, range index RIDX1 is not used because columns C1 and C2 both belong to table T1.
(4) Example 4
- Definition of range indexes
-
CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE
- Example SQL statement
-
SELECT * FROM "T1" WHERE "C1" BETWEEN 10 AND 30
- Explanation:
-
In this example, range index RIDX1 is used to skip chunks and segments that do not contain data that satisfies the conditions.
(5) Example 5 (range indexes are not used)
- Definition of range indexes
-
CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE
- Example SQL statement
-
SELECT * FROM "T1" WHERE "C1" BETWEEN "C2" AND "C3"
- Explanation:
-
When columns from the same table are specified in value expression 1 and value expression 2 or 3, a range index is not used (chunks and segments that do not contain data that satisfies the conditions are not skipped). In this example, range index RIDX1 is not used because columns C1, C2, and C3 all belong to table T1.
(6) Example 6
- Definition of range indexes
-
CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE
- Example SQL statement
-
SELECT * FROM "T1" WHERE "C1" IN (10,20,30)
- Explanation:
-
In this example, range index RIDX1 is used to skip chunks and segments that do not contain data that satisfies the conditions.
(7) Example 7 (range indexes are not used)
- Definition of range indexes
-
CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE
- Example SQL statement
-
SELECT * FROM "T1" WHERE "C1" IN ("C2",20,30)
- Explanation:
-
When columns from the same table are specified in value expression 1 and value expression 2 or any subsequent value expression, a range index is not used (chunks and segments that do not contain data that satisfies the conditions are not skipped). In this example, range index RIDX1 is not used because columns C1 and C2 both belong to table T1.
(8) Example 8
- Definition of range indexes
-
CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE
- Example SQL statement
-
SELECT * FROM "T1" WHERE "C1" LIKE CURRENT_USER SELECT * FROM "T1" WHERE "C1" LIKE ? SELECT * FROM "T1" WHERE "C1" LIKE 'ABC%' SELECT * FROM "T1" WHERE "C1" LIKE 'ABC%E' SELECT * FROM "T1" WHERE "C1" LIKE 'ABC%E%' SELECT * FROM "T1" WHERE "C1" LIKE 'ABC%E%G' SELECT * FROM "T1" WHERE "C1" LIKE 'ABC_' SELECT * FROM "T1" WHERE "C1" LIKE 'ABC_E' SELECT * FROM "T1" WHERE "C1" LIKE 'ABC_E_' SELECT * FROM "T1" WHERE "C1" LIKE 'ABC_E_G' SELECT * FROM "T1" WHERE "C1" LIKE 'ABC' SELECT * FROM "T1" WHERE "C1" NOT LIKE ? SELECT * FROM "T1" WHERE "C1" NOT LIKE 'ABC%' SELECT * FROM "T1" WHERE "C1" LIKE 'AB\_C%' ESCAPE '\' SELECT * FROM "T1" WHERE "C1" NOT LIKE 'AB\_C%' ESCAPE '\'
- Explanation:
-
In this example, range index RIDX1 is used to skip chunks and segments that do not contain data that satisfies the conditions when any of these SELECT statements is executed.
If a dynamic parameter is specified in the LIKE predicate, the range index is used. However, if the pattern character string that does not satisfy the following conditions is specified, there is no benefit to using range indexes:
-
LIKE
• A pattern character string that starts with 'character-string%' (% is a special character)
• A pattern character string that starts with 'character-string_' (_ is a special character)
• A pattern character string that does not contain special character % or _ (complete match)
-
NOT LIKE
• The pattern character string 'character-string%' (% is a special character)
-
(9) Example 9 (range indexes are not used)
- Definition of range indexes
-
CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE
- Example SQL statement
-
SELECT * FROM "T1" WHERE "C1" LIKE '%ABC%' SELECT * FROM "T1" WHERE "C1" LIKE '%ABC' SELECT * FROM "T1" WHERE "C1" LIKE '_ABC_' SELECT * FROM "T1" WHERE "C1" LIKE '_ABC' SELECT * FROM "T1" WHERE "C1" NOT LIKE CURRENT_USER SELECT * FROM "T1" WHERE "C1" NOT LIKE 'ABC%E' SELECT * FROM "T1" WHERE "C1" NOT LIKE 'ABC%E%' SELECT * FROM "T1" WHERE "C1" NOT LIKE 'ABC%E%G' SELECT * FROM "T1" WHERE "C1" NOT LIKE '%ABC%' SELECT * FROM "T1" WHERE "C1" NOT LIKE '%ABC' SELECT * FROM "T1" WHERE "C1" NOT LIKE 'ABC_' SELECT * FROM "T1" WHERE "C1" NOT LIKE 'ABC_E' SELECT * FROM "T1" WHERE "C1" NOT LIKE 'ABC_E_' SELECT * FROM "T1" WHERE "C1" NOT LIKE 'ABC_E_G' SELECT * FROM "T1" WHERE "C1" NOT LIKE '_ABC_' SELECT * FROM "T1" WHERE "C1" NOT LIKE '_ABC' SELECT * FROM "T1" WHERE "C1" NOT LIKE 'ABC' SELECT * FROM "T1" WHERE "C1" LIKE '%AB\_C%' ESCAPE '\' SELECT * FROM "T1" WHERE "C1" LIKE 'A\%B@_C%' ESCAPE CASE WHEN 10=? THEN '\' ELSE '@' END SELECT * FROM "T1" WHERE "C1" NOT LIKE '%AB\_C%' ESCAPE '\'
- Explanation:
-
None of the SELECT statements shown above satisfies the condition for LIKE predicate in No. 1 in Table 5‒4: Conditions under which range indexes are used. Therefore, range index RIDX1 is not used (chunks and segments that do not contain data that satisfies the conditions are not skipped).
(10) Example 10 (range indexes are not used)
- Definition of range indexes
-
CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE
- Example SQL statement
-
SELECT * FROM "T1" WHERE "C1"=10 OR "C2"=20 SELECT * FROM "T1" WHERE NOT("C1"=10)
- Explanation:
-
When the predicate specified in the condition uses the logical operator OR or NOT, a range index is not used (chunks and segments that do not contain data that satisfies the conditions are not skipped).
(11) Example 11 (range indexes are not used)
- Definition of range indexes
-
CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE
- Example SQL statement
-
SELECT * FROM "T1" WHERE "T1"."C1"+10=20
- Explanation:
-
When a scalar operation containing a range-indexed column is used, no range index is used (chunks and segments that do not contain data that satisfies the conditions are not skipped).
(12) Example 12 (range indexes are not used)
- Definition of range indexes
-
CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE
- Example SQL statement
-
SELECT * FROM "T1","T2","T3" WHERE "T1"."C1" BETWEEN "T2"."C2" AND "T3"."C3" SELECT * FROM "T1","T2","T3" WHERE "T1"."C1" IN ("T2"."C2","T3"."C3")
- Explanation:
-
When columns from three or more different tables are specified in the predicates, a range index is not used (chunks and segments that do not contain data that satisfies the conditions are not skipped).
(13) Example 13
- Definition of range indexes
-
CREATE INDEX "RIDX1" ON "T1"("C2") IN "DBAREA01" EMPTY INDEXTYPE RANGE CREATE INDEX "RIDX2" ON "T1"("C3") IN "DBAREA01" EMPTY INDEXTYPE RANGE CREATE INDEX "RIDX3" ON "T2"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE
- Example SQL statement
-
SELECT * FROM "T1" "X" WHERE "X"."C1"=ANY(SELECT "T2"."C1" FROM "T2" WHERE "X"."C2"=10 AND "X"."C3"="T2"."C1")
- Explanation:
-
Range indexes RIDX1 and RIDX2 will not be used because their indexed columns (columns C2 and C3 of table T1) are specified as external reference columns.
Range index RIDX3 will be used to skip chunks and segments that do not contain data that satisfies the conditions because its indexed column (column C1 of table T2) is not specified as an external reference column.
(14) Example 14
- Definition of range indexes
-
CREATE INDEX "RIDX1" ON "T2" ("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE
- Example SQL statement
-
SELECT * FROM "T1","T2" WHERE "T1"."C1"<"T2"."C1" SELECT * FROM "T1" INNER JOIN "T2" ON "T1"."C1"<"T2"."C1"
- Explanation:
-
If a nested loop join that uses table T1 as an outer table and table T2 as an inner table is applied, range index RIDX1 is used to skip chunks and segments. For details about a nested loop join, see 5.5.1 About nested-loop join.
(15) Example 15
- Definition of range indexes
-
CREATE INDEX "RIDX1" ON "T2" ("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE
- Example SQL statement
-
SELECT * FROM "T1","T2" WHERE "T1"."C1"="T2"."C1" SELECT * FROM "T1" INNER JOIN "T2" ON "T1"."C1"="T2"."C1"
- Explanation:
-
If a hash join that uses table T1 as an outer table and table T2 as an inner table is applied as the table joining method, range index RIDX1 is used to skip chunks and segments. For details about a hash join, see 5.5.2 About hash join.
(16) Example 16
- Definition of range indexes
-
CREATE INDEX "RIDX1" ON "T1" ("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE
- Example SQL statement
-
SELECT * FROM "T1" "X" WHERE EXISTS(SELECT * FROM "T2" WHERE "X"."C1"="T2"."C1")
- Explanation:
-
If hash execution is applied as the processing method to a subquery that contains an external reference column, range index RIDX1 is used to skip chunks and segments. For details about hash execution as the processing method of a subquery that contains an external reference column, see (3) Hash execution in 5.6.3 Methods for processing subqueries that contain an external reference column.
(17) Example 17
- Definition of range indexes
-
CREATE INDEX "RIDX1" ON "T1" ("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE
- Example SQL statement
-
SELECT * FROM "T1" WHERE "C1" IN (SELECT "T2"."C1" FROM "T2") SELECT * FROM "T1" WHERE "C1"=ANY(SELECT "T2"."C1" FROM "T2")
- Explanation:
-
If hash execution is applied as the processing method to a subquery that does not contain an external reference column, range index RIDX1 is used to skip chunks and segments. For details about hash execution as the processing method of a subquery that does not contain an external reference column, see (4) Hash execution in 5.6.1 Methods for processing subqueries that do not contain an external reference column.