Secondary Index Access Summarized by Example | Teradata Vantage - Secondary Index Access Summarized by Example - Advanced SQL Engine - Teradata Database

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.

The following is a snapshot instance of this table.

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