MergeBlockRatio - Advanced SQL Engine - Teradata Database

Database Utilities

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update
Product Category
Teradata Vantage™

Specifies the maximum size of a new data block that results from the merging of several smaller blocks. The size is expressed as the ratio of the new block size to the maximum block size specified in the table definition or, if no maximum size is specified in the table definition, by the PermDBSize setting in DBS Control.

Data blocks are never merged for spool, global temporary, or volatile tables.

Field Group

File System

Valid Settings

Setting Description
0 Resets MergeBlockRatio to the Teradata default, currently 60%.
1 through 100% The maximum size of a data block that results from merged smaller blocks, as a percentage of the maximum data block size for the table.



Changes Take Effect

When the DBS Control record is written.

Usage Notes

During normal database operations, the data blocks that store table rows on cylinders can split and shrink, resulting in many blocks which are far smaller than the maximum allowed data block size. Full table modify operations for tables with several small data blocks require more disk I/O than would be required if the table rows were stored on fewer and larger data blocks. Vantage can merge the small data blocks of these tables automatically during full table modify operations, which can result in reduced I/O overhead and improved database performance.

The maximum size for a multirow data block can be defined on a per-table basis with the DATABLOCKSIZE option of CREATE TABLE and ALTER TABLE statements. The maximum size for data blocks in tables that do not have a specified DATABLOCKSIZE is defined by the PermDBSize field in DBS Control.

Data block merging applies only to permanent and permanent journal tables.

The MergeBlockRatio is the largest size to which Vantage will merge small data blocks, expressed as a proportion of this maximum data block size.

The merge block ratio also can be defined on a per-table basis with the MERGEBLOCKRATIO option of CREATE TABLE and ALTER TABLE statements. The ratio used for tables that do not have a specified MERGEBLOCKRATIO is defined by the MergeBlockRatio field in DBS Control.

If DisableMergeBlocks is TRUE, the MergeBlockRatio field setting is ignored.

Related Information

For more information on... See...
the DATABLOCKSIZE and MERGEBLOCKRATIO options of CREATE TABLE and ALTER TABLE Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
PermDBSize field PermDBSize.
Disabling automatic data block merging DisableMergeBlocks.