DWHPro Insights

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

By Roland Wenzlofsky ·

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.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Column partitioning was introduced in Teradata 14.00; later releases expanded the feature. Check the release-specific definition and restrictions.

DATABLOCKSIZE specifies a logical block-size setting, subject to release/platform limits and storage overhead. It is not a universal exact count of physical 512-byte disk sectors.

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.

Larger blocks may reduce I/O overhead for scans, but evaluate caching, compression and concurrent workload before choosing a size.

Smaller blocks may reduce unnecessary bytes read for selective access, while increasing other overhead. Measure representative access patterns rather than treating this as a guaranteed improvement.

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

2 thoughts on “Teradata DATABLOCKSIZE: How to Minimize Disk I/Os and Optimize Workload Requirements”

  1. How data blocksize affects the insert/update/delete and select operations?
    Now that teradata has full fallback and compressed systems, what is the impact of data blocksize?

    Reply
  2. The default size set for data block in Teradata is 254 sectors(127 KB). If you specify other values it will be displayed in the create table definition.

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam