5.3.3 Whether to use a single-column index or a multiple-column index

(1) When it is better to define a single-column index

To retrieve data using a single column as the key, define a single-column index.

For example, for the retrieval described in the following, define a single-column index on column C1.

SQL specification example

SELECT * FROM "T1"
     WHERE "C1" = 1

B-tree index definition example

CREATE INDEX "T1IX_C1"
     ON "T1" ("C1" ASC) IN DBAREA01 EMPTY

To Page Top

(2) When it is better to define a multiple-column index

If using a single column would not sufficiently narrow down the rows to be retrieved, use multiple columns as keys to define a multiple-column index if doing so allows you to more efficiently narrow down the rows to be retrieved.

For example, for the retrieval described below, if a single-column index is defined on columns C1 and C2 separately, only one of the B-tree indexes for either column C1 or C2 can be used at any one time. For such cases, define a multiple-column index (T1IX_C1C2) consisting of columns C1 and C2.

SQL specification example

SELECT * FROM "T1"
      WHERE "C1" = 1 AND "C2" = 'A'

B-tree index definition example

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

When a multiple-column index is used to determine whether values satisfy the search condition, they are evaluated according to the order in which the columns were defined in the multiple-column index. Therefore, when defining a multiple-column index, determine the order in which to specify the columns by considering factors such as the search conditions you will be specifying.

For example, to execute the SQL specification example shown above, we recommend that you execute the CREATE INDEX statement as described in the B-tree index definition example that is also shown.

To Page Top

(3) Notes on using a multiple-column index

This subsection explains the differences in how the data range is narrowed down based on the search conditions.

When using a multiple-column index to narrow down the rows to be searched, how the search range is narrowed down differs depending on the search conditions specified in the WHERE clause of the SELECT statement. When specifying the columns to index, specifying columns with frequent = condition specifications first will more effectively narrow down the search range.

The figure below shows the relationship between the specification of a search condition with a WHERE clause and the search range.

B-tree index definition example

CREATE INDEX "T1IX_C1C2C3"
        ON "T1"("C1" ASC,"C2" ASC,"C3" ASC) IN DBAREA01 EMPTY

Figure 5‒6: Search condition with a WHERE clause and the search range of B-tree indexes

#1

Because the condition for column C2 is not = condition or IS NULL condition, the condition for column C3 is not used for narrowing down the search range.

#2

Because there is no condition for column C2, the condition for column C3 is not used for narrowing down the search range.

#3

Because the condition for column C1 is not = condition or IS NULL condition, the conditions for columns C2 and C3 are not used for narrowing down the search range.

#4

Because there is no condition for column C1, the conditions for columns C2 and C3 are not used for narrowing down the search range.

To Page Top