15.00 - MERGEBLOCKRATIO - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

MERGEBLOCKRATIO

The MERGEBLOCKRATIO option provides a way to combine existing small data blocks into a single larger data block during full table modification operations for permanent tables and permanent journal tables. This option is not available for volatile and global temporary tables. The file system uses the merge block ratio that you specify to reduce the number of data blocks within a table that would otherwise consist mainly of small data blocks. Reducing the number of small data blocks enables Teradata Database to reduce the I/O overhead of operations that must read and modify a large percentage of the table. It is not possible to define a general block size that would be considered to be small in this context. The exact block size that fits this description is somewhat subjective and can differ based on the I/O performance that occurs at different sites.

For tables that are frequently modified by inserting new data, the average block size varies between 50% and 100% of their maximum supported multirow block size. This maximum supported size is defined as either the table-level attribute DATABLOCKSIZE (see “DATABLOCKSIZE” on page 525) or the system-level block size for the table as defined in the DBS Control record if you have not established a value for the DATABLOCKSIZE attribute for a table.

Note that when you delete rows from a table, the blocks that previously held the deleted rows can become smaller than the sizes in the defined range, and their new sizes can vary considerably from your user-defined block size.

An extreme example (extreme because it is uncommon for requests to access every data block in a table) of a block size distribution that benefits from having its data blocks merged together is a table whose blocks were created with an average size of 64 KB, but which have gradually shrunk or split over time to an average size of only 8 KB. If block splits were common for this table, its number of data blocks can have increased by an order of magnitude.

If the rows in the table could be repackaged into blocks whose size were closer to the original average block size, you would probably experience improved response times for queries that read most or all of the blocks in the table because the number of logical or physical reads (or both) would then be reduced by an order of magnitude as well.

Merging blocks automatically augments the existing functionality of the ALTER TABLE statement DATABLOCKSIZE option (with or without the IMMEDIATE option). Although this option lessens the problem of tables overrun with small blocks, it suffers from various drawbacks.

  • Resolving the small data blocks problem requires a DBA to manually execute new SQL requests.
  • Resolution further requires research using the SHOWBLOCKS command of the Ferret utility (see Utilities: Volume 1 (A-K)), for example, to determine which particular tables and block sizes are causing performance problems before you can submit the SQL requests necessary to resolve the problem.
  • The operations performed by the problem resolving SQL request require an EXCLUSIVE table‑level lock, which then blocks concurrent update activity on the table.
  • If you specify the IMMEDIATE option (see “IMMEDIATE DATABLOCKSIZE” on page 46), there is no way to prohibit some data blocks in the table from becoming smaller as soon as the table is updated.
  • If you do not specify the IMMEDIATE option (see “IMMEDIATE DATABLOCKSIZE” on page 46), some data blocks in the table might take a long time to grow larger again if they are not updated for some time.
  • The merge block ratio approach has none of these drawbacks and offers the following enhanced functionality, all of which is autonomic.

  • It can run automatically without DBA intervention.
  • It does not require the analysis of block size histograms.
  • It does not require any specific AMP‑level locking.
  • It continuously searches for small data blocks to merge together even when some of those blocks are not being updated.
  • See “Performance Aspects of Merging Data Blocks” on page 530 for more information about performance‑related aspects of the MERGEBLOCKRATIO option.

    The size threshold at which small data blocks are merged can be controlled either using the MERGEBLOCKRATIO option or by changing the setting of the MergeBlockRatio DBS Control flag (see Utilities: Volume 1 (A-K) for details). It is also possible to deactivate the merging of small data blocks by changing the setting of the DisableMergeBlocks field of the DBS Control record (see Utilities: Volume 1 (A-K) for details).