Hitachi

Hitachi Advanced Database Application Development Guide


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.