12.21.6 Notes about using compressed tables
- When data in compressed columns is manipulated by SQL statements and utilities, overhead is required for compression and expansion processing. You can check the statistical information about a UAP provided by the statistics analysis utility (pdstedit) to determine the time required for compressing and expanding BINARY type data. Note that the processing time cannot be checked for an abstract data type (XML type).
- The data compression efficiency becomes higher as the split compression size is increased, although this also depends on the nature of the data to be compressed. If the split compression size is large, more process private area is required to execute some SQL statements# that involve storing and extracting data in compressed columns. In order to avoid a memory shortage, specify an appropriate split compression size taking into account the available memory in the system and the value of the pd_max_access_tables operand. For details about the increase in the process-specific memory requirement, see 15.1.6(9) Determining the size of the memory required to execute data manipulation SQL statements on compressed columns for a HiRDB/Single Server and 15.2.6(9) Determining the size of the memory required to execute data manipulation SQL statements on compressed columns for a HiRDB/Parallel Server.
- If an error occurred during table reorganization processing and an unload data file (file unloaded by pdrorg -k rorg) that contains compressed data is to be reloaded during the course of responding to the error, the table's compression specifications (whether compression specifications are used and the specified split compression size) must be the same for the unload source and the reload target. If the table contains a column whose compression specifications differ, pdrorg will terminate with an error.
- If a compressed table is to be rebalanced in shared mode, the time required for the processing might be longer when the table contains compressed columns than when the table does not contain any compressed columns because of the time required for data compression and expansion processing. If you want to reduce the execution time, use exclusive mode.
- #: This applies to the following SQL statements:
- SQL statements using the SUBSTR function
- SQL statements using the POSITION function
- SQL statements involving backward deletion/updating