As part of full-table modify operations, where the system sequentially scans the table as part of the operation, Teradata Database can merge small DBs into larger ones as the modifications are performed. While this can make some of the initial modifications to the table more expensive, it makes future full table modifications and queries cheaper by reducing the number of DBs in the table, which reduces disk I/Os.
You can control the frequency of merges and the size of resulting data blocks by:
- Setting the system-level MergeBlockRatio field in DBS Control.
- Specifying the table-level MergeBlockRatio attribute in the CREATE TABLE or ALTER TABLE statement.The table-level attribute overrides the system-level setting for any table that has the attribute. However, if the Disable Merge Blocks field in DBS Control is enabled, both the table-level attribute and the system-level setting will be ignored.
MergeBlockRatio settings allow you to make adjustments so that merged data blocks end up at a reasonable size (that is, the block does not split again quickly after it undergoes a merge).
To consider the right MergeBlockRatio setting, you must take into account the following:
- How often you expect to access or modify certain tables
- How much of the data is being modified
- The desired general size of blocks across certain tables