2.3.3 Range indexes

A range index manages the following two ranges (the minimum and maximum values of a column's data) for the data stored in the base table for which the range index is defined:

Range of data stored in a chunk
Range of data stored in a segment

The following figure provides an overview of the ranges that are managed by a range index.

Figure 2‒8: Overview of the ranges managed by a range index

When a range index is used, retrieval processing of chunks that store ranges of data that do not satisfy the search condition is skipped (chunks can be skipped).

After those chunks are skipped, retrieval processing of segments that store ranges of data that do not satisfy the search condition, within the chunks that store ranges of data that satisfy the search condition, is also skipped (segments can be skipped).

Skipping retrieval processing of unnecessary chunks and segments improves overall retrieval performance.

Chunk skipping

This refers to skipping retrieval processing of chunks storing ranges of data that do not satisfy the search condition.

For an overview of chunks, see 2.14.2 Managing data in data-import units (chunks). For details about chunk skipping, see 2.14.3 Relationship between chunks and range indexes.
Segment skipping

This refers to skipping retrieval processing of segments storing ranges of data that do not satisfy the search condition.

For an overview of segments, see (1) Segment in 2.4.3 DB area structure (segments and pages). The details about segment skipping are described below.

Note

■ Relationship between a range index and out-of-order execution

An SQL statement that is executed by out-of-order execution first reads the range of data stored in the chunks managed with a range index at the start of retrieval processing (when the cursor opens). Then, the statement stores the range in memory. During retrieval, the statement determines whether to perform chunk skipping based on the information stored in memory.

Note that this processing occurs only if the range index meets all of the following conditions:

The range index is defined in the table subject to retrieval performed by using a B-tree index.
The range index is used for chunk skipping.

Organization of this subsection

(1) Range index overview
(2) Range index characteristics
(3) Updating the range in a range index (segment)

(1) Range index overview

Using segment skipping as an example, the following figure provides an overview of range indexes.

Figure 2‒9: Range index overview (segment skipping)

To Page Top

(2) Range index characteristics

A range index has the four major characteristics described below. For the characteristics of range indexes that can be used for skipping chunks, see (2) Range index characteristics (skipping of chunks) in 2.14.3 Relationship between chunks and range indexes.

(a) Using B-tree indexes or text indexes together with range indexes

After chunk skipping is performed by using a range index, a B-tree index or text index can be used. For details about retrieval using a range index together with a B-tree index or text index, see (a) Using B-tree indexes or text indexes together with range indexes in (2) Range index characteristics (skipping of chunks) in 2.14.3 Relationship between chunks and range indexes.

Note: If a range index is used together with a B-tree index or text index, retrieval using a range index (segment skipping) is not performed.

(b) Multiple range indexes can be used

The following figure provides an example of a retrieval that uses multiple range indexes.

Figure 2‒10: Example of a retrieval that uses multiple range indexes

(c) Smaller data size than a B-tree index or a text index

In a B-tree index, a single index key is created for an entire row in the table. In a text index, each table row is managed by assigning a number to each character string contained in that row.

In a range index, by contrast, one range is created for each segment. Therefore, the data size is smaller in range indexes than in B-tree indexes or text indexes.

(d) Retrieval performance varies depending on the range of data stored in the segment

The performance of a retrieval that uses a range index depends on the range of data stored in the segment. The following figure provides an example.

Figure 2‒11: Example in which retrieval performance varies depending on the range of data stored in the segment

Explanation: In data storage example 1, retrieval processing is performed in two segments. In contrast, in data storage example 2, there are duplicate ranges and the range in each segment is wider than that in data storage example 1. Consequently, retrieval processing is performed in four segments. In this way, retrieval performance varies depending on the ranges of the data stored in the segments. Narrowing the ranges improves retrieval performance.

To Page Top

(3) Updating the range in a range index (segment)

When addition and update processing are performed repeatedly on rows, the ranges in the range index might expand, and as a result the benefits of using the range index might begin to diminish (the ranges never become narrower). When deletion processing is performed repeatedly on rows, the range index's ranges might become too wide for the ranges of data stored in the segments, and as a result the benefits of using the range index will diminish.

When the benefits of using a range index diminish, re-create the range index by performing index rebuilding.

The following table shows the conditions that result in the expansion of the range index's ranges.

Table 2‒3: Conditions that expand the ranges in a range index (segment)
No.	Operation	Condition that expands ranges in a range index
1	Row addition	When a value that is outside a range is added, the range of the segment that stores the added row expands to accommodate the added value.
2	Row update	When a value is updated so that it is no longer within a range, the range of the segment that stores the updated row expands to accommodate the updated value.
3	Row deletion	No range expansion occurs.

To Page Top