5.2.4 Points to consider in defining a multi-chunk table

(1) Advantages of defining a base table as a multi-chunk table

Defining a base table as a multi-chunk table brings the following advantages:

• You can search data and import data simultaneously (you can use the background-import facility).

• You can use the PURGE CHUNK statement to delete all data in a chunk.

• When you cannot search data, you can import the data so that you can search it at any time later. For an example of this type of operation, see 11.4.24 Operation taking background import and chunks into consideration (Example 2: Using chunks in wait status).

Note

If you frequently store only small amounts of data (4 megabytes or less), consider using the INSERT data manipulation SQL statement instead of the background-import facility. Frequently storing small amounts of data by using background import generates wasted areas, lowering the data storage efficiency. This is because background import reserves and allocates a new segment even if there is free space in already allocated segments.

If you define a base table as a column store table for which the INSERT, UPDATE, or DELETE statement will probably be executed, define the base table as a column store table that is a multi-chunk table. A column store table for which the INSERT, UPDATE, or DELETE statement is executed repeatedly might need to be reorganized. If the table is a multi-chunk table, you can perform reorganization on a chunk basis. This brings the following advantages:

• A shorter time is required for reorganization.

• Less disk space is required for reorganization.

Also, if you define a base table as an archivable multi-chunk table, you can use the chunk archiving function. If you intend to define a base table as an archivable multi-chunk table, read 5.2.5 Points to consider in defining an archivable multi-chunk table [Row store table] after reading this section.

To Page Top

(2) Disadvantages of defining a base table as a multi-chunk table

• Primary keys and unique indexes cannot be defined for multi-chunk tables.

• Management and maintenance of chunks are required. For example, chunks must be merged periodically. For details, see 11.4.9 Merging chunks (to reduce the number of chunks).

To Page Top

(3) Item that must be specified in the CREATE TABLE statement (maximum number of chunks)

When you define a base table as a multi-chunk table, specify the maximum number of chunks in the chunk specification in the CREATE TABLE statement.

To Page Top

(4) Determining the maximum number of chunks

A new chunk is created each time background import is executed using the adbimport command. Multiple created chunks can also be merged into a single chunk by using the adbmergechunk command. Note that you cannot create more chunks than the maximum-number-of-chunks value specified for chunk-specification in the CREATE TABLE statement.

Therefore, you need to estimate the value of maximum-number-of-chunks based on the following three factors:

• How often background import is executed using the adbimport command

• How often chunks are merged using the adbmergechunk command

• How long chunks are retained

You can use the PURGE CHUNK statement to delete created chunks. You can then create a number of new chunks equivalent to the number you deleted. The maximum-number-of-chunks value specified in the CREATE TABLE statement can be changed by using the ALTER TABLE statement.

Use the following formula to determine the value for the CHUNK_NUM variable. Then, specify the value determined by the formula or a larger value for maximum-number-of-chunks in the CREATE TABLE statement.

Formula (count)

CHUNK_NUM =
    ↑years x ↑(import_num ÷ merge_source_chunk_num)↑ x safe_rate↑
    + (↑merge_source_chunk_num x safe_rate↑)#

#: A certain amount of leeway is built into this formula to account for merge-source chunks that might remain as deletion-pending chunks after execution of the adbmergechunk command.

Important

Merge-source chunks that are not deleted when the adbmergechunk command is executed remain as deletion-pending chunks. This reduces the number of chunks you can use by a number equivalent to the number of deletion-pending chunks. For this reason, you need to consider the number of deletion-pending chunks when determining the value of the CHUNK_NUM variable. For details about deletion-pending chunks, see (3) Using the adbmergechunk command to merge chunks in 11.4.9 Merging chunks (to reduce the number of chunks).

Explanation of variables

years

The number of years over which the chunks (data stored in the base table) are to be retained.

Specify the number of years that must elapse after a chunk has been created by background import using the adbimport command before the chunk can be deleted by the PURGE CHUNK statement.

import_num

How many times a year background import will be executed using the adbimport command.

merge_source_chunk_num

The number of merge-source chunks present when the adbmergechunk command is executed.

For example, if you use the adbimport command to perform background import once a day and use the adbmergechunk command to merge chunks once every five days, use the number 5 for this value.

safe_rate

Safety factor Use 1.2 for this value.

The following shows examples of how the CHUNK_NUM variable is determined.

■ Example of determining the CHUNK_NUM variable

In this example, the CHUNK_NUM variable is determined for the following conditions:

• Chunks (the data stored in the base table) are retained for two years.

• Background import is executed once a week using the adbimport command.

• Four chunks are merged once a month using the adbmergechunk command.

• The safety factor is set to 1.2.

Formula (chunks)

In this example, the value of the CHUNK_NUM variable is 39. Accordingly, specify 39 or a larger value for maximum-number-of-chunks.

To Page Top