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, the majority of tables fluctuate in size throughout their lifetime because of the mix of insert, update, and delete workloads that operate on them. When you delete rows from a table, its data blocks can shrink to a size that is significantly smaller than half the defined maximum for the table. Once data blocks reach a size that is smaller than half their defined maximum, they remain small unless a roughly equal number of rows are added to the table. When a table has many such small data blocks, update and read operations against it must read a larger number of data blocks than would be necessary if those blocks were larger, and this causes the performance of updates to the table to become increasingly poor. The role of the merge block ratio is to enable the Teradata 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.
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 files. 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. It is not possible to 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 either the table-level attribute DATABLOCKSIZE 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. The default percentage for the merge block ratio is 60, while its maximum value is 100. whether specified using the MERGEBLOCKRATIO option for CREATE TABLE or the DBS Control parameter MergeBlockRatio. For more information, see Teradata Vantage™ - Database Utilities, B035-1102.
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 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.
- Resolving the small data blocks problem requires a DBA to manually execute 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 using the SHOWBLOCKS command of the Ferret utility or the equivalent SQL interface using the CreateFsysInfoTable and PopulateFsysInfoTable macros. For information on the Ferret utility, see Teradata Vantage™ - Database Utilities, B035-1102. For information on the CreateFsysInfoTable and PopulateFsysInfoTable macros, see Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210.
- 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 some data blocks in the table from becoming smaller as soon as the table is updated. If you do not specify the ALTER TABLE option IMMEDIATE, some data blocks in the table might take a long time to grow larger again if they are not updated for some time. See ALTER TABLE (Basic Table Parameters).
- 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 together even when some of those blocks are not 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 or by changing the setting of the MergeBlockRatio DBS Control parameter. Note that you can set the merge block ratio percentage as low as 1% using either the MergeBlockRatio DBS Control parameter or 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. To disable data block merging for all tables on your system, set the DBS Control parameter DisableMergeBlocks to TRUE.
Tables that are defined with a very small data block size do not gain a significant benefit 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.