Hitachi

Hitachi Advanced Database Setup and Operation Guide


5.3 Designing a B-tree index

This section explains what must be considered when designing a B-tree index.

Since B-tree indexes greatly affect application performance, you must carefully choose the columns for which B-tree indexes will be defined.

Important

A B-tree index defined for a column store table can be used to search that column store table only in limited circumstances. The following are typical cases in which a B-tree index is used for a column store table. We recommend that you define a B-tree index for a column store table only when the range of the processing target can be narrowed as shown in these cases.

  • Case where an index that will be used for index specification is specified when an SQL statement is executed

  • Case where the minimum value is obtained by specifying the set function MIN or the maximum value is obtained by specifying the set function MAX

  • Case where the UPDATE or DELETE statement is executed by specifying a search condition

In cases such as accessing data for a specific year or month, we recommend that you define range indexes and use them to narrow the search range.

For details about the rules that govern the use of B-tree indexes when retrieving data from column store tables, see B-tree indexes and text indexes used during execution of SQL statements in the HADB Application Development Guide.

Organization of this section