15.10 - MERGEBLOCKRATIO - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

This is the merge block ratio to be used for this table when Teradata Database combines smaller data blocks into a single larger data block.


DEFAULT
The value for MergeBlockRatio that is defined by the DBS Control record at the time a data block merge operation on this table begins. For details, see Support Utilities, B035-1180. 
You can specify DEFAULT MERGEBLOCKRATIO for global temporary and volatile tables.
Whether Teradata Database uses the merge block ratio you specify depends on the setting for the DBS Control parameter DisableMergeBlocks.
  • If DisableMergeBlocks is FALSE and you specify a MERGEBLOCKRATIO, Teradata Database uses the value you specify. If you do not specify a MERGEBLOCKRATIO, Teradata Database uses the system-wide default setting for the DBS Control parameter MergeBlockRatio.
  • If DisableMergeBlocks is TRUE, Teradata Database ignores all table-level settings for the merge block ratio and does not merge data blocks for any table in the system.

    For details, see Support Utilities, B035-1180.

integer PERCENT
Merge block ratio when a data block merge operation occurs on this table. 
The range for integer is from 1 through 100. The default value is 60.
You can only specify a numeric merge block ratio for permanent base tables and permanent journal tables. You cannot specify a numeric merge block ratio for global temporary or volatile tables. This value of the merge block ratio does not affect the resulting block size when only a single block is modified. Setting the merge block ratio for a table to too high a value can cause the resulting merged block to require splitting during subsequent modifications.
PERCENT is an optional keyword to indicate value is a percentage.
NO
Data blocks for the table are not merged when Teradata Database combines smaller data blocks into a single larger data block.

You can specify NO MERGEBLOCKRATIO options for global temporary and volatile tables.

Example: Modifying MERGEBLOCKRATIO

These examples demonstrate how to use ALTER TABLE requests to modify the merge block ratio setting for an individual table.

The first example changes the default merge block ratio for emp_table so that the system retrieves the current value for the DBS Control parameter MergeBlockRatio and applies it whenever it merges smaller data blocks into a larger data block.

     ALTER TABLE emp_table, DEFAULT MERGEBLOCKRATIO;

The next example modifies the current setting of the merge block ratio for emp_table to 25%.

     ALTER TABLE emp_table, MERGEBLOCKRATIO = 25 PERCENT;

The final example disables all data block merging for emp_table .

     ALTER TABLE emp_table, NO MERGEBLOCKRATIO;