15.00 - Primary Index Reads - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Primary Index Reads

The following flowchart illustrates the flow of a generic primary index read. The flowchart begins at the point that the data block containing the searched row has been located.

Example : Primary Index Read

This example illustrates how the Teradata file system uses the primary index to eliminate synonyms from the returned result by performing a row hash search.

The original query is the following:

     SELECT *
     FROM employee
     WHERE employee_number = 3755;

At this point in the process, the master index lookup showed that the row representing employee number 3755 is stored on cylinder 169. The cylinder index lookup showed that the row is in the six sector block beginning with sector number 789.

The following graphic illustrates the logical representation of that data block.

The file system must locate the row having a row hash value of 1000 and a primary index data value of 3755. The following process describes how the file system undertakes this final task.

1 The file system begins scanning the data block beginning at sector 789.

2 The first row found with a row hash value of 1000 has an index data value of 1006. This is a hash synonym for the searched row.

Because this is not the row specified by the WHERE clause predicate, the scan continues.

3 The next row found with a row hash value of 1000 has an index data value of 3755.

Because this is the requested row and it was accessed using a UPI, the data block scan terminates and the row is returned to the requestor. Had the index been a NUPI, the file system would have had to read another row to know that no further rows matched.