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 in a table that otherwise consists mainly of small data blocks. Reducing the number of small data blocks enables Vantage to reduce the I/O overhead of operations that must read and modify a large percentage of the table.
You cannot define a general block size that is considered to be small in this context. The exact block size that fits this description is 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 the table-level attribute DATABLOCKSIZE (see CREATE TABLE (Table Options Clause)).
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 8 KB. This example is extreme because requests rarely 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 are repackaged into blocks whose size is closer to the original average block size, you typically 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) is reduced by an order of magnitude.
- Resolving the small data blocks problem requires a DBA to manually run 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 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 IMMEDIATE, data blocks may become smaller when the table is updated. If you do not specify IMMEDIATE, data blocks may take a long time to grow larger if not updated for a while. This topic has additional information about 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, even when not all are 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 using the MERGEBLOCKRATIO option.
MINIMUM DATABLOCKSIZE
This specification changes the data block size for a table to the minimum possible value for your system. The option sets the maximum data block size for blocks that contain multiple rows to the minimum legal value of 21,504 bytes (42 sectors) for systems running with large cylinders or 9,216 bytes (18 sectors) for systems running without large cylinders.
IMMEDIATE DATABLOCKSIZE
The only reason to change the data block size for a table is to enhance performance. Repacking the data blocks of large tables is a time-consuming process, so specifying the IMMEDIATE option for large tables substantially increases the amount of time required for the ALTER TABLE request to complete.
Field upgrades of systems do not change the data block size from 64 KB to 127.5 KB until a block splits. To take immediate advantage of the performance enhancements offered by the 127.5 KB block size, you must force the data block size change directly.
To upgrade the data block size from 63.5 KB to 127.5 KB, perform one of the following ALTER TABLE requests on every table in every database.
ALTER TABLE database_name.table_name, DEFAULT DATABLOCKSIZE IMMEDIATE; ALTER TABLE database_name.table_name, DATABLOCKSIZE = 127.5 KBYTES IMMEDIATE;
If you do not specify the IMMEDIATE keyword, the definition for DATABLOCKSIZE is set to 127.5 KB, but the size increase does not occur until rows are inserted into the newly defined table.
Because of the extra time required to process requests with the IMMEDIATE option, plan to convert your data block sizes during non-peak hours.
When an ALTER TABLE ... IMMEDIATE ends, or you end it, there may be data blocks of their original size and data blocks of the newly specified size.
The DATABLOCKSIZE value returned in response to a SHOW TABLE request is the value specified in the most recently entered ALTER TABLE or CREATE TABLE request.
If no DATABLOCKSIZE specification is specified in the ALTER TABLE request, then the data block size is not changed and no data blocks are repacked.