2.14.3 Relationship between chunks and range indexes

This section explains the relationship between chunks and range indexes. For details about range indexes, see 2.3.3 Range indexes.

A range index manages the range of data stored within segments of a base table (the minimum and maximum values of a column's data), as well as the range of data stored within chunks. By using a range index, you can skip retrieval processing of chunks and segments that store ranges of data that do not satisfy a search condition. This improves retrieval performance.

The following figure provides an overview of range indexes, chunks, and segments.

Figure 2‒48: Overview of range indexes, chunks, and segments

Explanation

A range index is defined for column C2 of table T1. Executing background import on table T1 stores data in the table. A range index is also created based on the data in column C2. These groups of data are managed as a single chunk.

In this case, the range index manages the range of table data within segments as well as the range of data within the chunk.

Organization of this subsection

(1) Retrieval using a range index (skipping of chunks)
(2) Range index characteristics (skipping of chunks)
(3) Updating the ranges in a range index (chunk and segment)

(1) Retrieval using a range index (skipping of chunks)

When a table is retrieved using a range index, retrieval processing of chunks and segments that store ranges of data that do not satisfy the search condition can be skipped in stages, as described in the following.

■ Retrieval processing using a range index

Skipping of chunks
Skipping of segments

Important: A range index defined in a version earlier than version 02-02 cannot skip chunks.

The following describes these two types of retrieval processing.

Skipping of chunks

In table retrieval using a range index, retrieval processing can first be skipped in units of chunks. HADB determines whether a chunk stores ranges of data that satisfy the search condition, and does not retrieve the chunk if it stores ranges of data that do not satisfy the search condition.

In this case, retrieval performance improves because table data, B-tree index data, and text index data are not accessed.

The following figure provides an overview of chunk skipping.

Figure 2‒49: Overview of chunk skipping
Skipping of segments

If a comparison between a search condition and the range specified by a range index shows that chunk 3 satisfies the condition, retrieval processing is performed on the table segments within chunk 3. During this process, the retrieval of segments that store ranges of data that do not satisfy the search condition can be skipped in the same way as described in 2.3.3 Range indexes.

The following figure provides an overview of segment skipping.

Figure 2‒50: Overview of segment skipping

To Page Top

(2) Range index characteristics (skipping of chunks)

This section explains the characteristics of a range index that can skip chunks, and the four major characteristics of a range index explained in (2) Range index characteristics under 2.3.3 Range indexes.

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

Range indexes that can skip chunks can be used together with B-tree indexes and text indexes.

Also described below is retrieval processing that uses a B-tree index or a text index.

■ Retrieval using a range index that can skip chunks and a B-tree index

Retrieval using a range index (skipping of chunks)

First, retrieval using a range index is executed. This process determines whether a chunk stores ranges of data that satisfy the search condition, and it skips retrieval of the chunk if it stores ranges of data that do not satisfy the search condition. Skipping chunks narrows the chunks to those that satisfy the search condition.
Retrieval using a B-tree index

After the chunks have been narrowed to those that satisfy the search condition, retrieval is performed using the B-tree index within the target chunks.

■ Retrieval using a range index that can skip chunks and a text index

Retrieval using a range index (skipping of chunks)

First, retrieval using a range index is executed. This process determines whether a chunk stores ranges of data that satisfy the search condition, and it skips retrieval of chunks that store ranges of data that do not satisfy the search condition. Skipping chunks narrows the chunks to those that satisfy the search condition.
Retrieval using a text index

After the chunks have been narrowed to those that satisfy the search condition, retrieval is performed using the text index within the target chunks.

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) Using multiple range indexes

There is no difference. For details about using multiple range indexes, see (b) Multiple range indexes can be used in (2) Range index characteristics under 2.3.3 Range indexes.

(c) When the amount of data is less than that in the B-tree index or text index

There is no difference. For details about situations when the amount of data is less than that in the B-tree index or text index, see (c) Smaller data size than a B-tree index or a text index in (2) Range index characteristics under 2.3.3 Range indexes.

(d) Variation in retrieval performance depending on the ranges of data stored in chunks

The performance of retrieval using a range index varies depending on the ranges of data stored in chunks.

Figure 2‒51: Examples in which retrieval performance varies depending on the ranges of data stored in chunks

Explanation: In data storage example 1, retrieval processing is performed on a single chunk. On the other hand, in data storage example 2, there is a range overlap and the range in each chunk is wider than in storage example 1. Therefore, retrieval processing is performed on three chunks. In this way, the retrieval performance varies depending on the range of data stored in chunks even when the same search condition is used.

To Page Top

(3) Updating the ranges in a range index (chunk and segment)

When addition and update processing are performed repeatedly on rows, the ranges of chunks and segments managed by the range index expand. As a result, the benefits of using the range index might begin to diminish (that is, 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 chunks and segments, and as a result, the benefits of using the range index 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‒20: Conditions that expand the ranges in a range index (chunks and segments)
No.	Operation	Condition that expands ranges in a range index
1	Row addition	When a value outside a range is added, the range of the chunk and segment that store 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 chunk and segment that store the updated row expands to accommodate the updated value.
3	Row deletion	No range expansion occurs.

To Page Top