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 CREATE TABLE) 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 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. This example is extreme because it is uncommon for requests to access every data block in a table. 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.
- Resolving the small data blocks problem requires a DBA to manually execute new SQL requests.
- You must also 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 “File System Information Macros” in 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 IMMEDIATE option, some data blocks in the table may become smaller as soon as the table is updated. If you do not specify the IMMEDIATE option, some data blocks in the table might take a long time to grow larger again if they are not updated for some time. See IMMEDIATE DATABLOCKSIZE.
- 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.
For more information about performance-related aspects of the MERGEBLOCKRATIO option, see Performance Aspects of Merging Data Blocks.
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. 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. For more information, see Teradata Vantage™ - Database Utilities , B035-1102 .