Scalable Database Server, HiRDB Version 8 Installation and Design Guide

[Contents][Index][Back][Next]

13.2.3 Single-column index vs. multicolumn index

The two types of indexes are single-column indexes and multicolumn indexes. A single-column index is an index based on the values in one column of a table. A multicolumn index is an index based on the values in multiple columns of a table.

Organization of this subsection
(1) Creating a single-column index
(2) Creating a multicolumn index

(1) Creating a single-column index

A single-column index should be created when retrieval will be executed using one column only as the key.

(2) Creating a multicolumn index

A multicolumn index should be created in the cases discussed below.

(a) Retrieval of data that satisfies multiple conditions

A multicolumn index should be created when data satisfying multiple conditions is to be retrieved, such as when a complex-condition retrieval using the AND operator with multiple columns as the key is executed.

For example, suppose that a complex-condition search is to be executed using table columns C1, C2, and C3 as the key items:

SELECT retrieval-column FROM retrieval-table WHERE C1=10 AND C2=20 AND C3=30

In this case, a multicolumn index consisting of the three columns C1, C2, and C3 should be created instead of creating three separate single-column indexes. In this way, overhead for index and row accesses can be reduced.

When a complex-condition retrieval is to be executed, it is important that the column for which the equals (=) condition is specified be defined as the first component column of the multicolumn index. Then the column that is next most likely to have the equals condition should be specified, followed by the third column, and so on. As a result, the retrieval range can be reduced within the index, thereby reducing the retrieval time. If the equals condition is not specified for the first component column of a multicolumn index, appropriate retrieval results may not be obtained from the index. In this case, better results may be achieved by using a single-column index.

(b) Grouping or sorting data after narrowing the data with a search condition

A multicolumn index should be created using the columns specified as the search condition then the columns to be grouped or sorted, in this order.

Suppose that a complex condition retrieval is executed using table columns C1 and C2 as the key, and then the retrieval results are sorted in descending order of C3 and ascending order of C4, as shown as follows:

SELECT retrieval-column FROM retrieval-table WHERE C1=10 AND C2=20
  ORDER BY C3 DESC,C4 ASC

In this case also, a multicolumn index consisting of columns C1, C2, C3, and C4 should be created, instead of creating two single-column indexes in columns C1 and C2. The data in column C3 should be sorted in descending order, and the data in column C4 should be sorted in ascending order, so that overhead for index and row accesses is reduced.

(c) Duplicated multicolumn indexes created for one table

If a multicolumn index consisting of columns C1 and C2 is created for a table together with another multicolumn index consisting of columns C1 and C3, overhead increases when the duplicated column, C1, is updated. To reduce this overhead, one multicolumn index consisting of C1, C2, and C3 should be created.

Note that if the table is retrieved using columns C1 and C3 as the search conditions, retrieval performance may be reduced.

(d) Priority between single-column and multicolumn indexes

If both single-column and multicolumn indexes are created for the same table, HiRDB uses the indexes in the priority order shown in Table 13-4. This table assumes that search condition C1=10 AND C2=20 is specified for table retrieval.

Table 13-4 Priority among single-column and multicolumn indexes

Columns constituting the index Priority
Component column 1 Component column 2 Component column 3
C1 C2 None 1
C1 C3 C2 2
C1 None None 3
C1 C3 None 4
C3 C2 None 5