The MERGEBLOCKRATIO option provides a way to combine existing small data blocks into a single larger data block during full table modification operations for permanent tables and permanent journal tables. This option is not available for volatile and global temporary tables. The file system uses the merge block ratio that you specify to reduce the number of data blocks within a table that would otherwise consist mainly of small data blocks. Reducing the number of small data blocks enables Teradata Database to reduce the I/O overhead of operations that must read and modify a large percentage of the table. It is not possible to define a general block size that would be considered to be small in this context. The exact block size that fits this description is somewhat subjective and can differ based on the I/O performance that occurs at different sites.
For tables that are frequently modified by inserting new data, the average block size varies between 50% and 100% of their maximum supported multirow block size. This maximum supported size is defined as either the table-level attribute DATABLOCKSIZE (see “DATABLOCKSIZE” on page 525) or the system-level block size for the table as defined in the DBS Control record if you have not established a value for the DATABLOCKSIZE attribute for a table.
Note that when you delete rows from a table, the blocks that previously held the deleted rows can become smaller than the sizes in the defined range, and their new sizes can vary considerably from your user-defined block size.
An extreme example (extreme because it is uncommon for requests to access every data block in a table) of a block size distribution that benefits from having its data blocks merged together is a table whose blocks were created with an average size of 64 KB, but which have gradually shrunk or split over time to an average size of only 8 KB. If block splits were common for this table, its number of data blocks can have increased by an order of magnitude.
If the rows in the table could be repackaged into blocks whose size were closer to the original average block size, you would probably experience improved response times for queries that read most or all of the blocks in the table because the number of logical or physical reads (or both) would then be reduced by an order of magnitude as well.
Merging blocks automatically augments the existing functionality of the ALTER TABLE statement DATABLOCKSIZE option (with or without the IMMEDIATE option). Although this option lessens the problem of tables overrun with small blocks, it suffers from various drawbacks.
The merge block ratio approach has none of these drawbacks and offers the following enhanced functionality, all of which is autonomic.
See “Performance Aspects of Merging Data Blocks” on page 530 for more information about performance‑related aspects of the MERGEBLOCKRATIO option.
The size threshold at which small data blocks are merged can be controlled either using the MERGEBLOCKRATIO option or by changing the setting of the MergeBlockRatio DBS Control flag (see Utilities: Volume 1 (A-K) for details). It is also possible to deactivate the merging of small data blocks by changing the setting of the DisableMergeBlocks field of the DBS Control record (see Utilities: Volume 1 (A-K) for details).