5.2.3 Examples of indexes that are used during retrieval of a table (examples of index priority)

(1) Example 1 (priority between single-column indexes)

The following is an example of the relative priority between B-tree indexes (single-column indexes):

Example of the B-tree index definition:

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

Example of the SELECT statement to be executed:

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

In this example, the B-tree index IDX_C1 has a priority of 15, and the B-tree index IDX_C2 has a priority of 2. As such, the B-tree index IDX_C2 is used for retrieval.

Important

In the example above, if only one of the B-tree indexes is selected to be used on the basis of the priority, change the search condition, if possible, in such a manner that the B-tree index that can narrow the search most effectively will be used. You can expect an improvement in performance by using a B-tree index that provides more effective narrowing.

To Page Top

(2) Example 2 (priority between single-column indexes)

The following is an example of the relative priority between B-tree indexes (single-column indexes):

Example of the B-tree index definition:

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

Example of the SELECT statement to be executed:

 SELECT * FROM "T1"
     WHERE "C2"='U0100' AND "C1"=100

In this example, B-tree indexes IDX_C1 and IDX_C2 both have a priority of 2. Because a B-tree index for the first column specified in the search condition has the higher priority, B-tree index IDX_C2 is used for retrieval.

To Page Top

(3) Example 3 (priority between multiple-column indexes)

The following is an example of the relative priority between B-tree indexes (multiple-column indexes):

Example of the B-tree index definition:

 CREATE INDEX "IDX_C1C2"
       ON "T1" ("C1","C2")
     IN "DBAREA01"
     EMPTY
 
 CREATE INDEX "IDX_C2C3"
       ON "T1" ("C2","C3")
     IN "DBAREA01"
     EMPTY

Example of the SELECT statement to be executed:

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

In this example, B-tree index IDX_C2C3 is used.

IDX_C1C2 has column C2 as its second indexed column, while IDX_C2C3 has column C2 as its first indexed column, so IDX_C2C3 is used.

To Page Top

(4) Example 4 (priority between single-column index and multiple-column index)

The following is an example of the relative priority between single-column and multiple-column B-tree indexes:

Example of the B-tree index definition:

 CREATE INDEX "IDX_C1"
       ON "T1" ("C1")
     IN "DBAREA01"
     EMPTY
 
 CREATE INDEX "IDX_C3C2"
       ON "T1" ("C3","C2")
     IN "DBAREA01"
     EMPTY

Example of the SELECT statement to be executed:

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

In this example, B-tree index IDX_C1 is used.

IDX_C3C2 has column C2 as its second indexed column, while IDX_C1 has column C1 as its indexed column, so IDX_C1 is used.

To Page Top

(5) Example 5 (priority between single-column index and multiple-column index)

The following is an example of the relative priority between single-column and multiple-column B-tree indexes:

Example of the B-tree index definition:

 CREATE INDEX "IDX_C1"
       ON "T1" ("C1")
     IN "DBAREA01"
     EMPTY
 
 CREATE INDEX "IDX_C2C3"
       ON "T1" ("C2","C3")
     IN "DBAREA01"
     EMPTY

Example of the SELECT statement to be executed:

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

In this example, the B-tree index IDX_C1 has a priority of 15, and the B-tree index IDX_C2C3 has a priority of 2. As such, the B-tree index IDX_C2C3 is used.

IDX_C1 that has column C1 as an indexed column and IDX_C2C3 that has column C2 as the first indexed column are subject to comparison to determine priority, as shown in Table 5‒1: Index priority when multiple indexes are defined for a table.

To Page Top

(6) Example 6 (priority between text index and B-tree index)

The following is an example of the relative priority of a text index and B-tree index (single-column index).

Example of index definitions:

 CREATE INDEX "IDX_TXT_C1"      <- Definition of text index
       ON "T1" ("C1")
     IN "DBAREA01"
     EMPTY
     INDEXTYPE TEXT

 CREATE INDEX "IDX_C2"          <- Definition of B-tree index
       ON "T1" ("C2")
     IN "DBAREA01"
     EMPTY

Example of SELECT statement to execute:

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

In this example, the text index IDX_TXT_C1 has a priority of 8, and the B-tree index IDX_C2 has a priority of 4. As such, the B-tree index IDX_C2 is used for retrieval.

Important

In this scenario, if the relative priority results in only one of the indexes being used, if possible, change the search condition so that the index that can narrow the search scope most effectively is used. You can expect to see an improvement in performance by using an index that more efficiently narrows the search scope.

To Page Top

(7) If you want to change the index to be used during retrieval

This subsection explains how to change the index to be used during retrieval.

To Page Top