Setting Data Block Merging Limits - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-11-03
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantageā„¢

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