5.3.2 Examples of range indexes used during retrieval

CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE

SELECT * FROM "T1" WHERE "C1">10

In this example, range index RIDX1 is used to skip chunks and segments that do not contain data that satisfies the conditions.

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

SELECT * FROM "T1" WHERE "C1"=10 AND "C2">20 AND "C3">30

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.

CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE

SELECT * FROM "T1" WHERE "C1">"C2"

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.

CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE

SELECT * FROM "T1" WHERE "C1" BETWEEN 10 AND 30

In this example, range index RIDX1 is used to skip chunks and segments that do not contain data that satisfies the conditions.

CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE

SELECT * FROM "T1" WHERE "C1" BETWEEN "C2" AND "C3"

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.

CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE

SELECT * FROM "T1" WHERE "C1" IN (10,20,30)

In this example, range index RIDX1 is used to skip chunks and segments that do not contain data that satisfies the conditions.

CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE

SELECT * FROM "T1" WHERE "C1" IN ("C2",20,30)

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.

CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE

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 '\'

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)

CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE

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 '\'

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).

CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE

SELECT * FROM "T1" WHERE "C1"=10 OR "C2"=20
SELECT * FROM "T1" WHERE NOT("C1"=10)

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).

CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE

SELECT * FROM "T1" WHERE "T1"."C1"+10=20

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).

CREATE INDEX "RIDX1" ON "T1"("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE

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")

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).

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

SELECT * FROM "T1" "X"
    WHERE "X"."C1"=ANY(SELECT "T2"."C1" FROM "T2"
                       WHERE "X"."C2"=10 AND "X"."C3"="T2"."C1")

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.

CREATE INDEX "RIDX1" ON "T2" ("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE

SELECT * FROM "T1","T2" WHERE "T1"."C1"<"T2"."C1"
SELECT * FROM "T1" INNER JOIN "T2" ON "T1"."C1"<"T2"."C1"

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.

CREATE INDEX "RIDX1" ON "T2" ("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE

SELECT * FROM "T1","T2" WHERE "T1"."C1"="T2"."C1"
SELECT * FROM "T1" INNER JOIN "T2" ON "T1"."C1"="T2"."C1"

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.

CREATE INDEX "RIDX1" ON "T1" ("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE

SELECT * FROM "T1" "X"
    WHERE EXISTS(SELECT * FROM "T2" WHERE "X"."C1"="T2"."C1")

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.

CREATE INDEX "RIDX1" ON "T1" ("C1") IN "DBAREA01" EMPTY INDEXTYPE RANGE

SELECT * FROM "T1" WHERE "C1" IN (SELECT "T2"."C1" FROM "T2")
SELECT * FROM "T1" WHERE "C1"=ANY(SELECT "T2"."C1" FROM "T2")

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.