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

• 5.3.1 Notes on defining B-tree indexes (unfinished status of B-tree indexes)

• 5.3.2 Points to consider in determining the columns to be defined for a B-tree index

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

• 5.3.4 Allocating an unused area inside a B-tree index page (PCTFREE)

• 5.3.5 Points to consider in determining the columns to be defined for a unique index

• 5.3.6 Setting a null-value exclusion specification (EXCLUDE NULL VALUES)

To Page Top