Teradata DATABLOCKSIZE: How to Minimize Disk I/Os and Optimize Workload Requirements

What is the Teradata DATABLOCKSIZE Option?

Most of us are familiar with the Teradata option DATABLOCKSIZE, included in the CREATE TABLE statement. However, only a few know how to use it to reduce disk I/Os. This article provides a brief overview of how to use this option effectively.

Teradata stores data in data blocks consisting of multiple sectors on a disk cylinder.

Normally, a data block contains rows from a single table only, with no intermixing of rows from different tables. As such, the block header of each data block includes a table ID that identifies the table to which the block belongs.

Columnar storage was introduced in Teradata 14.10, allowing the values of one or more columns from multiple rows to be compressed into a single data block.

The DATABLOCKSIZE option specifies the number of sectors composing a data block, each with a default size of 512 bytes. Teradata appends an additional 512-byte sector to the data block when it reaches full capacity, continuing until the maximum block size is reached.

Teradata will split a data block into two blocks when it reaches the maximum block size.

What does this mean in practice? The same number of rows can be stored in a few large data blocks or in many smaller ones. Inserting rows into smaller data blocks may lead to more frequent block splits.

The optimal data block size depends on your workload requirements.

Tables accessed through full table scans should have a larger data block size, as this allows multiple rows to be transferred efficiently into AMP memory.

If your table has many distinct index accesses retrieving only a few rows at a time, it is advisable to use smaller data blocks to avoid unnecessarily loading large amounts of data into AMP memory during retrieval.