Adjusting the DATABLOCKSIZE and MERGEBLOCKRATIO Table Parameters
You can control the default size for multirow data blocks on a table-by-table basis in a CREATE TABLE or ALTER TABLE request using the DATABLOCKSIZE and MERGEBLOCKRATIO options:
Disk arrays can scan at higher rates if the I/Os are larger, but larger I/Os can be less efficient for row-at-a-time access which requires that the entire data block be read for the relatively few bytes contained in a row. Cylinder reads enable smaller data blocks for row-at-a-time access and large reads for scans.
For average workloads, the benefits of using large data blocks outweighs the small penalty associated with row-at-a-time access up to 64 KB. Setting the data block size requires more judgment and analysis for 128KB and 1MB data blocks where the penalty for row-at-a-time access becomes measurable.
|
IF you specify… |
THEN… |
|
DATABLOCKSIZE in CREATE TABLE |
the data block can grow to the size specified in DATABLOCKSIZE instead of being limited to the global PermDBSize (see “PermDBSize” in Utilities). For any row, Teradata Database uses only the data block size required to contain the row. Note: If you use block level compression, adjust DATABLOCKSIZE to the maximum, 255 sectors. |
|
DATABLOCKSIZE in ALTER TABLE |
the data blocks can grow to the size specified in DATABLOCKSIZE when the row size requires the growth. Whether data blocks are adjusted to that new size immediately or gradually over a long period of time depends on the use of the IMMEDIATE clause. |
|
MERGEBLOCKRATIO in CREATE TABLE |
Teradata Database limits attempts to combine blocks if the result is larger than the specified percent of the maximum multirow data block size. |
|
MERGEBLOCKRATIO in ALTER TABLE |
the size of the resulting block when multiple existing data blocks are being merged has a upper limit. The limit depends on whether Teradata Database determines that logically adjacent data blocks can be merged with the single data block being modified or not. Data blocks can still be initially loaded at the PermBDSize specification or the data block size specified with the DATABLOCKSIZE option. Merges occur only during full‑table modifications. |
|
the IMMEDIATE clause |
the rows in all existing data blocks of the table are repacked into data blocks using the newly specified size. For large tables, this can be a time‑consuming operation, requiring spool space to accommodate 2 copies of the table while it is being rebuilt. If you do not specify the IMMEDIATE clause, existing data blocks are not modified. When individual data blocks of the table are modified as a result of user transactions, the new value of DATABLOCKSIZE is used. Thus, the table changes over time to reflect the new data block size. |
To specify the global data block size, use PermDBSize (see “PermDBSize” in Utilities).