17.00 - 17.05 - About Setting Data Block Merging Limits - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval

As part of full-table modify operations, where the system sequentially scans the table as part of the operation, Vantagecan 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