MergeBlockRatio - Advanced SQL Engine - Teradata Database

Purpose

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.

Field Group

File System

Valid Settings

Default

60%

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. Teradata Database 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 Teradata Database 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 Topics