5.2.2 Examples of indexes that are used during retrieval of a table

(1) Example 1 (B-tree index(single-column indexes))

Example of the B-tree index definition:

 CREATE INDEX "IDX_C1"
        ON "T1" ("C1")
      IN "DBAREA01"
      EMPTY

Example of the SELECT statement to be executed:

 SELECT "C1","C2","C3" FROM "T1"
     WHERE "C1"=100
 
 SELECT "C1","C2","C3" FROM "T1"
     WHERE "C1">100 AND "C2"='U0100'

When the SELECT statement shown above is executed, B-tree index IDX_C1 is used.

■ When a B-tree index is not used

A B-tree index is not used in the following case.

Example of the B-tree index definition:

 CREATE INDEX "IDX_C1"
        ON "T1" ("C1")
      IN "DBAREA01"
      EMPTY

Example of the SELECT statement to be executed:

 SELECT "C1","C2","C3" FROM "T1"
     WHERE "C1">100 OR "C2"='U0100'

With an OR condition, if a B-tree index is defined only for column C1 (or only for column C2), that B-tree index is not used. Therefore, when the SELECT statement shown above is executed, B-tree index IDX_C1 is not used.

If B-tree indexes are defined for both columns C1 and C2, those B-tree indexes are not used.

To Page Top

(2) Example 2 (B-tree index (multiple-column indexes))

Example of the B-tree index definition:

 CREATE INDEX "IDX_C2C1"
       ON "T1" ("C2","C1")
     IN "DBAREA01"
     EMPTY

Example of the SELECT statement to be executed:

 SELECT "C1","C2","C3" FROM "T1"
     WHERE "C1"=100
 
 SELECT "C1","C2","C3" FROM "T1"
     WHERE "C2"='U0100'
 
 SELECT "C1","C2","C3" FROM "T1"
     WHERE "C1">100 AND "C2"='U0100'
 
 SELECT "C1","C2","C3" FROM "T1"
     WHERE "C1">100 OR "C2"='U0100'

If the SELECT statement shown above is executed, B-tree index IDX_C2C1 is used.

(1) and (2) above are typical examples of cases in which a B-tree index is used and in which a B-tree index is not used. Even in the case above where the B-tree index is used, the B-tree index might not actually be used depending on the format of the specified search condition. For details about how to determine the B-tree index that was actually used during retrieval, see 3.2.5 How to check the index that was used for retrieval.

To Page Top

(3) Example 3 (text index)

Example of text index definition:

 CREATE INDEX "IDX_TXT_C1"
        ON "T1" ("C1")
      IN "DBAREA01"
      EMPTY
      INDEXTYPE TEXT

Example of SELECT statement to execute:

 SELECT "C1","C2","C3" FROM "T1"
     WHERE "C1" LIKE '%XYZ%'

 SELECT "C1","C2","C3" FROM "T1"
     WHERE "C1" LIKE '%XYZ%' AND "C2"='U0100'

When you execute this SELECT statement, HADB uses the text index IDX_TXT_C1.

▪ Scenario in which a text index is not used

In the following scenario, the text index will not be used.

Example of text index definition:

 CREATE INDEX "IDX_TXT_C1"
        ON "T1" ("C1")
      IN "DBAREA01"
      EMPTY
      INDEXTYPE TEXT

Example of SELECT statement to execute:

 SELECT "C1","C2","C3" FROM "T1"
     WHERE "C1" LIKE '%XYZ%' OR "C2"='U0100'

In the case of an OR condition, if a text index is defined only for column C1 (or only for column C2), then that text index will not be used. Consequently, when you execute this SELECT statement, HADB does not use the text index IDX_TXT_C1.

The examples described in (1) to (3) are representative examples of situations in which indexes are used and not used. In any of these examples in which the index is used, the index might not be used if the search condition is written in a certain way. If you want to check the index that will actually be used for a retrieval, see 5.2.5 How to check the index used during execution of an SQL statement.

To Page Top