MERGEBLOCKRATIO Option | VantageCloud Lake - MERGEBLOCKRATIO with CREATE TABLE (Table Options Clause) - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

For Object File System tables, MERGEBLOCKRATIO has a default setting that you cannot change.

When the size of a table only grows through SQL INSERT operations and bulk data loads and never becomes smaller, the average size of its data blocks varies from half their maximum up to the maximum size permitted according to the values you specify for the DATABLOCKSIZE option when you define the table. See DATABLOCKSIZE.

However, most tables fluctuate in size because of insert, update, and delete operations. When you delete rows from a table, its data blocks can become significantly smaller than half the defined maximum for the table.

Data blocks smaller than half their defined maximum remain small unless an approximately equal number of rows are added to the table. When a table has many such small data blocks, update and read operations against the table must read a larger number of data blocks than if those blocks were larger, causing the performance of updates to the table to become increasingly poor. The role of the merge block ratio is to enable the file system to merge such small data blocks into larger data blocks dynamically, but only when a full-table modification occurs on a table. Having fewer data blocks with a larger size reduces the number of I/O operations required to read a set of rows from disk.

MERGEBLOCKRATIO combines existing small data blocks into a single larger data block during full table modification operations for permanent tables and permanent journal tables. The file system uses the merge block ratio that you specify to reduce the number of data blocks within a table that otherwise consist mainly of small data blocks. You cannot define a general block size that is "small" in this context. The exact block size that fits this description is subjective and can differ based on the I/O performance of different systems, different workloads, and different numbers of active users.

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 the table-level attribute DATABLOCKSIZE. The default percentage for the merge block ratio is 60, while its maximum value is 100.

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 the block size you specified when you created or last altered the table definition.

An extreme example 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 blocks can have increased by an order of magnitude. Requests rarely access every data block in a table.

If the rows in the table can be repackaged into blocks whose size is closer to the original average block size, response times for queries that read most or all of the blocks in the table typically improve, because the number of logical or physical reads (or both) is reduced by an order of magnitude.

Merging blocks automatically is an enhancement to ALTER TABLE DATABLOCKSIZE. See ALTER TABLE Usage Notes (Basic Table Parameters). This option can alleviate the problem of tables overrun with small data blocks, but also suffers from the following drawbacks:
  • Resolving the small data blocks problem requires a DBA to manually run new SQL requests.
  • You must determine which tables and block sizes are causing performance problems before you can submit the SQL requests necessary to resolve the problem. For example, table data block size statistics are available in the SQL interface using the CreateFsysInfoTable and PopulateFsysInfoTable macros.
  • 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 ALTER TABLE option IMMEDIATE, there is no way to prohibit data blocks in the table from becoming smaller when the table is updated. If you do not specify the ALTER TABLE option IMMEDIATE, data blocks in the table may take a long time to grow larger again if not updated frequently. See ALTER TABLE Usage Notes (Basic Table Parameters).
The merge block ratio approach has none of these drawbacks and offers the following enhanced functionality, all of which is autonomous.
  • Runs automatically without DBA intervention.
  • Does not require the analysis of block size histograms.
  • Does not require any specific AMP-level locking.
  • Continuously searches for small data blocks to merge, even when not all of those blocks are being updated.

You can control the size threshold at which small data blocks are merged either by using the MERGEBLOCKRATIO option with a CREATE TABLE or ALTER TABLE statement . You can set the merge block ratio percentage as low as 1% using the MERGEBLOCKRATIO option in an ALTER TABLE or CREATE TABLE statement. The default value for MERGEBLOCKRATIO is 60.

To disable data block merging for an individual table, specify the NO MERGEBLOCKRATIO option.

Tables defined with a small data block size do not benefit significantly from merging small data blocks, because the sizes of most data blocks in such tables are close to the value established for a MERGEBLOCKRATIO, so there are few opportunities to merge data blocks in those tables.