5.2.5 Points to consider in defining an archivable multi-chunk table [Row store table]
This subsection describes the points you need to consider when defining a base table as an archivable multi-chunk table.
Note that the description in this subsection assumes that you have already read the explanation in 5.2.4 Points to consider in defining a multi-chunk table.
- Organization of this subsection
(1) Advantages of defining a base table as an archivable multi-chunk table
The database size can be reduced by compressing the data in chunks.
(2) Disadvantages of defining a base table as an archivable multi-chunk table
-
Archived data cannot be updated by using the UPDATE statement and cannot be deleted by using the DELETE statement. To update or delete data in chunks, you must unarchive the data. For details about the conditions in which the UPDATE or DELETE statement results in an error, see the following sections in the manual HADB SQL Reference:
-
Rules in Specification format and rules for the UPDATE statement
-
Rules in Specification format and rules for the DELETE statement
-
-
A search in archived data takes more time than a search in non-archived data for the following reasons:
-
Time is required to expand the data.
-
Index-based search cannot be performed.
-
-
When you search an archivable multi-chunk table, narrow down the search range by specifying the information about the archive range column as the search condition in the SELECT statement. If you cannot specify the archive range column as a search condition in the SELECT statement, a search takes a long time because the search range cannot be narrowed down.
For the points to consider when searching an archivable multi-chunk table, see Considerations when searching an archivable multi-chunk table in the HADB Application Development Guide.
- Note
-
-
To change an archivable multi-chunk table to a single-chunk table, you must use the CREATE TABLE statement to redefine the table. Therefore, carefully consider the advantages and disadvantages, and then decide whether to define an archivable multi-chunk table.
-
When changing an archivable multi-chunk table to a regular multi-chunk table, you do not need to redefine the table by using the CREATE TABLE statement. You can change the table by using the ALTER TABLE statement.
-
(3) Conditions of data to be stored in an archivable multi-chunk table
In an archivable multi-chunk table, store chronological data that is generated every day. Chronological data here means sensor-originating data, shop sales data, and other data that consists of sequential records that are generated chronologically. Note that each record of chronological data must include datetime information, such as when the record was generated or when a product was sold.
(4) Creating an archive directory
Before you execute the CREATE TABLE statement, you must create an archive directory. Also, you must grant the HADB administrator the necessary access privileges for the archive directory. For details, see chunk-archive-specification in chunk-specification in Explanation of specification format in Specification format and rules for the CREATE TABLE statement in the manual HADB SQL Reference.
We recommend that you create an archive directory for each archivable multi-chunk table. Multiple archivable multi-chunk tables can share one archive directory. However, in such a case, concentration of access on one archive directory might degrade performance.
If you create archive directories on a performance-first basis, the following recommendations apply:
-
Create an archive directory for each archivable multi-chunk table.
-
Create only one archive directory in a file system dedicated for that archive directory.
-
Create only archive directories in a file system dedicated for those archive directories.
(5) Specification of the archive range column
When you define an archivable multi-chunk table, you must specify the archive range column. Note that a column having one of the following data types cannot be specified as the archive range column:
-
CHARACTER type (if the definition length is 33 bytes or longer)
-
VARCHAR type
-
BINARY type
-
VARBINARY type
The data stored in the archive range column is used to narrow down the search range when an archivable multi-chunk table is searched. Therefore, make sure that the column you want to use as the archive range column contains datetime information, such as when the record was generated or when a product was sold.
Note that NOT NULL constraint is applied to the archive range column.
(6) Range index automatically defined for the archive range column
The range index is automatically defined for the archive range column. For the range index, you do not need to prepare the dedicated data DB area, because the amount of data in the range index is small. Store the range index and other indexes defined for an archivable multi-chunk table in the same data DB area, unless there is a good reason to do otherwise.
In the chunk specification in the CREATE TABLE statement, specify the data DB area that will store the range index.
Note that you can specify an index identifier for the range index that is automatically defined for the archive range column. If you do not specify an index identifier for the range index, the HADB server automatically determines the index identifier.