DWHPro Insights

Teradata Physical Storage: Understanding the Technical Details for Performance Optimization

By Roland Wenzlofsky ·

Teradata Physical Storage: Understanding the Technical Details for Performance Optimization

A thorough understanding of the Teradata system’s technical intricacies is crucial to attaining expertise in performance optimization. Although many of us are knowledgeable about existing optimization opportunities, resolving complex performance issues often requires an in-depth understanding of internal data storage.

The physical medium may be HDD, SSD or a cloud storage service, depending on deployment. The logical storage structures described here should not be equated with the mechanical geometry of an HDD.

Check the storage architecture for the target platform. Logical cylinders, sectors and data blocks remain distinct from the underlying physical devices.

The Teradata data storage system comprises cylinders, sectors, and blocks within the sectors, which marks the start of our journey into its inner workings.

Data records are stored in blocks allocated to specific cylinders on the AMP’s disk.

Teradata Physical Storage – How can a table row be accessed?

teradata physical storage

The Parsing Engine sends requests to the AMPs containing the internal TABLEID, which serves as a unique identifier for tables in a Teradata system. To locate a data record on the disk, two indexes are employed.

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

The Teradata Master Index

The master index helps identify cylinders containing a table’s data. Cylinder indexes then describe the data blocks within their cylinders. These are different logical structures; their exact fields and caching behavior depend on the release.

The master index maintains a sub-index of available cylinders for write operations such as inserts and updates.

The master index is cached in the FSG cache and readily accessible on every AMP for data retrieval.

The Teradata Cylinder Index

After querying the master index, the AMP quickly locates the cylinders containing the table records. The cylinder index is then used to pinpoint the specific cylinder sectors that hold the necessary data blocks, which ultimately house the required data rows. Notably, the cylinder index stores the initial and final sectors for every data block in a table.

Due to the larger size of the cylinder index compared to the master index, it may not be completely stored within the FSG cache.

The lowest ROWID and highest ROWHASH are accessible for restricting access to sectors containing the necessary records, similar to the master index. The cylinder index maintains a sub-index of all vacant disk sectors.

Once the AMP determines the first and last sector of the data block containing the necessary data record(s), the block can be transferred to the FSG cache for performing select, update, insert, or delete operations.

The Binary Search

The AMP will locate the requested data record(s) by performing a binary search on the data block(s).

The binary search algorithm efficiently locates a row in a sorted array of row IDs by iteratively comparing the desired row ID value with the row ID value of the central pointer array element.

Pointers mark table rows. The row can be accessed by the position the pointer array’s central element points to if it matches the row ID being searched for. If the row ID is less than the central element’s row ID, the algorithm will search to the left of the middle element. If the search key is greater, it will search to the right. The algorithm repeats these steps until the searched row ID is found or there are no more elements to search, indicating that the row is unavailable.

In part two of “The Ultimate Teradata Physical Storage Guide” series, we’ll examine the data block structure, record storage within the blocks, and Teradata’s management of varying block size needs.

We will demonstrate Teradata’s data block maintenance techniques, which involve adding, reducing, and splitting blocks as necessary to reduce fragmentation and optimize system speed.

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.

1 thought on “Teradata Physical Storage: Understanding the Technical Details for Performance Optimization”

  1. Hallo Roland
    Ich finde es nach wie vor interessant, dass du sinnvolle kostensparende Features von Teradata erklärst, um dann untendran zu fragen, wer jetzt von Teradata auf andere Plattformen, die diese Features nicht oder weniger effizient haben, zu migrieren.

    Warum fragst du nicht, ob jemand wegen dieser Features nach Teradata migrieren möchte? “Help us map where the industry is heading.” sollte alle Möglichkeiten offenhalten, oder?

    Und PS1: ja, ich habe deine Statistik verfälscht, weil ich eine theoretische Kombination eingegeben habe, und das aktuelle Resultat sehen zu können.

    PS2: bei den heutigen Kunden beschränkt sich die physische Speicherung der Daten, die du mit Teradata nutzen willst, längst nicht mehr ausschliesslich auf den Block Store. –> ein Tipp für einen zukünftigen Artikel 😉

    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