5.5.1 Points to consider when defining a range index

(1) Cases that benefit from a range index

In a search that uses a range index, the data to be searched is narrowed based on ranges of values of the data in the column on which the range index has been defined. Therefore, define a range index in the following cases:

• When there is a column whose data is stored in the input data file in ascending or descending order (or more or less in ascending or descending order)

  For example, if a table contains a column that stores the data acquisition date, and typically these data acquisition dates are stored in the input data files in ascending or descending date order, defining a range index on that column will be beneficial. The following figure shows an example.

  Figure 5‒9: Case that benefits when a range index is defined (1 of 3)

  When searching a table, specify as a search condition a column for which a range index is defined (column C2 in the preceding figure). However, depending on the specified search condition, the range index might not be used. For details about the conditions under which range indexes are used, see Range indexes used during execution of SQL statements in the manual HADB Application Development Guide.

• When there is a column in all the input data files that always contains a predetermined range of data values

  For example, if a table contains a column that stores product numbers and the ranges of product number values is more or less predetermined in all the input data files, defining a range index on that column will be beneficial. The following figure shows an example.

  Figure 5‒10: Case that benefits when a range index is defined (2 of 3)

  When searching a table, specify as a search condition a column for which a range index is defined (column C2 in the preceding figure). However, depending on the specified search condition, the range index might not be used. For details about the conditions under which range indexes are used, see Range indexes used during execution of SQL statements in the manual HADB Application Development Guide.

• When data to be stored using background import is not duplicated among chunks

  Consider a case in which data collected by a sensor (a day's worth of data) is stored daily in a table, using background import. If the table targeted for background import contains a column that stores the data generation date and time, defining a range index for that column is effective. The following figure shows an example.

  Figure 5‒11: Case that benefits when a range index is defined (3 of 3)

  Explanation

  Each input data file stores a day's worth of data. When background import is executed, the data representing the data generation date and time stored in column C2 is not duplicated among the individual chunks.

  In this case, because the range is managed for each chunk, retrieval processing of chunks that store ranges of data that do not satisfy the search condition can be skipped. This allows retrieval to take place efficiently.

  When searching a table, specify as a search condition a column for which a range index is defined (column C2 in the preceding figure). However, depending on the specified search condition, the range index might not be used. For details about the conditions under which range indexes are used, see Range indexes used during execution of SQL statements in the manual HADB Application Development Guide.

To Page Top

(2) Cases that benefit when a range index is not defined

In the following cases, the range of each segment expands, with the result that defining a range index does not provide the benefit of segment skipping.

• When there are no columns in which the data in the input data files is arranged in more or less ascending or descending order

• When rows will be subject to frequent addition, update, and deletion processing

When rows are deleted, although the ranges of the values in the segments do not expand, the range of the range index itself becomes greater compared with the ranges of the data stored in the segments. In such a case, the benefits of using a range index will not be realized.

In the following case, the ranges of individual chunks overlap with each other, with the result that defining a range index does not provide the benefit of chunk skipping.

• When data to be stored using background data import is duplicated among chunks

Do not define a range index for any column that you do not expect to benefit from segment skipping or chunk skipping. Doing so reduces search performance.

To Page Top

(3) Columns for which a range index cannot be defined

You cannot define a range index based on the following types of columns:

• CHAR column whose definition length is 33 bytes or greater

• VARCHAR column

• BINARY column

• VARBINARY column

To Page Top