Basic structure of an index

An index consists of a key and key values. A key is the column name of an indexed column. The values in the column are called the key values. Creating an index for a column that will be used as the basis for retrievals from the table will improve the table's retrieval performance.

It is recommended that indexes be created for the following types of columns:

Column used as a condition for narrowing the range of data to be searched
Column used as a condition for joining tables
Column used as a condition for sorting or grouping data
Component column that defines a referential constraint (foreign key)

Indexes provided by HiRDB have a B-tree structure. The highest stage of an index in a B-tree structure is called a root page; the lowest stage is called a leaf page; a page in the middle is called a middle page. Root pages and middle pages point to pages at lower stages. A leaf page has a key value for each index page and its address.

Figure 3-21 shows the B-tree structure of an index.

Figure 3-21 B-tree structure of an index

[Figure]

Explanation: This is a B-tree structure index. The index is created in Step 3 on the basis of the product code column, PCODE is the key, and 101L to 671M are key values.

Organization of this subsection: (1) Single column index and multicolumn index; (2) Optimizing based on cost; (3) Index definition

(1) Single column index and multicolumn index

An index can be based either on a single column or on multiple columns (multicolumn index). A single column index is created by indexing the values in a single column of a table. A single column index is appropriate for retrievals that require a single column as the key. A multicolumn index is created by indexing the values in more than one column of a table. A multicolumn index is appropriate for the following purposes:

To narrow the data that is to be retrieved to data that satisfies multiple conditions
To group or sort the data that has been narrowed using a retrieval condition
When some of the same columns are used in multiple multicolumn indexes created for a table

(2) Optimizing based on cost

When multiple indexes have been created for a table, HiRDB selects and uses the index that has the least access cost and that it evaluates as being optimal for the conditions specified for the retrieval. This process in which HiRDB selects an index based on an evaluation of optimality is called optimizing based on cost. HiRDB evaluates the following access costs:

Hit rate based on the specified retrieval conditions
Number of input/output processes required for SQL processing
CPU load required for SQL processing

HiRDB performs optimizing based on cost in order to improve table retrieval performance. Even when an SQL with retrieval conditions specified is executed, table retrieval performance does not deteriorate. This means that it is possible to create UAPs without being concerned about index access priority. However, in order for HiRDB to use the optimal index, indexes must have been defined for those columns for which retrieval conditions are specified.

3.4.1 Basic structure of an index

(1) Single column index and multicolumn index

(2) Optimizing based on cost

(3) Index definition