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)