Query 3 - Teradata Database

Query 3

The third query uses the NUSI column name as the WHERE clause attribute, with the requested value being the name column value Smith.

     SELECT * 

     FROM Customer 

     WHERE Name = ‘Smith’;

1 The Dispatcher broadcasts an AMP retrieval step containing the NUSI value Smith to all AMPs.

2 The file system scans the NUSI subtable with row hash 432 and selects index rows for Smith in customer on each AMP, retrieving all the base table rowIDs and uniqueness values associated with the name column value of Smith and row hash value 432.

The steps are processed in parallel and one row is located on each AMP.

AMP1 (NUSI row hash=432, UV=8; Base table row hash=640, UV=1)

AMP2 (NUSI row hash=432, UV=3; Base table row hash=884, UV=1)

AMP3 (NUSI row hash=432, UV=1; Base table row hash=147, UV=1)

AMP4 (NUSI row hash=432, UV=5; Base table row hash=778, UV=7)

3 The file system directly retrieves the base table rows on each AMP in parallel and returns them to the requestor.

Cust=40, Name=Smith, Phone=222-3333

Cust=74, Name=Smith, Phone=555-6666

Cust=49, Name=Smith, Phone=111-6666

Cust=56, Name=Smith, Phone=555-7777