16.10 - Secondary Index Access Summarized by Example - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

This example indicates how a series of queries against a table can use various secondary indexes to access the rows in that table.

Configuration

The system for this example has four AMPs.

Table Definition

The table used in this demonstration is a simple three-column customer table, defined as follows.

Column Name Attribute Described Type of Index Defined on the Column
Cust Customer Number USI
Name Customer Last Name NUSI
Phone Customer Phone Number NUPI

The following is a snapshot instance of this table.

customer table
Customer
Cust Name Phone
USI NUSI NUPI
37 White 555-4444
98 Brown 333-9999
74 Smith 555-6666
95 Peters 555-7777
27 Jones 222-8888
56 Smith 555-7777
45 Adams 444-6666
31 Adams 111-2222
40 Smith 222-3333
72 Adams 666-7777
84 Rice 666-5555
49 Smith 111-6666
12 Young 777-7777
62 Black 444-5555
77 Jones 777-6666
51 Marsh 888-2222

Base table and secondary index subtable rows are distributed as illustrated by the following graphic.



Sample Queries

The following queries can all be answered without having to do a full-table scan. Note that uniqueness value is abbreviated UV throughout.

Query 1

The first query uses the NUPI column phone as the WHERE clause attribute, with the requested value being 555-7777.

     SELECT *
     FROM Customer
     WHERE Phone = ‘555-7777’;
  1. The hashing algorithm generates the row hash for this primary index access and finds that rows having the NUPI value 555-7777 hash to AMP 4.
  2. The Dispatcher sends an AMP retrieval step directly to AMP 4, where the file system retrieves two matching rows.

    The first matching row has row hash=778 and UV=3, while the second matching row has row hash=778 and UV=7. The row hash values are identical because this primary index is nonunique.

  3. The file system reads the requested rows and returns them to the requestor.

    Cust=95, Name=Peters, Phone=555-7777

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

Query 2

The second query uses the USI column Cust as the WHERE clause attribute, with the requested value being 95.

     SELECT *
     FROM Customer
     WHERE Cust = 95;
  1. The hashing algorithm generates the row hash for this USI access.

    The hash map indicates that an index row having the value 95 hashes to the customer USI subtable on AMP 3 with a row hash=588.

  2. The Dispatcher sends an AMP retrieval step directly to AMP 3, where the file system reads the subtable row having the row hash=588 to determine which AMP owns the base table row.
  3. The file system retrieves the base table row hash 778 and uniqueness value 3 from the USI subtable row and determines that the requested base table row is stored on AMP 4.
  4. The retrieval directive is passed to AMP 4 where the row for customer number 95, having rowID value 778,3 is located.
  5. The file system reads the requested row and returns it to the requestor.

    Cust=95, Name=Peters, Phone=555-7777

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