Example: SQL Request - Analytics Database - Teradata Vantage

Database Introduction

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-09-27
dita:mapPath
gtm1628096154303.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
dsm1472253642401
lifecycle
latest
Product Category
Teradata Vantage™

Consider the following Teradata SQL requests using a table of checking account information. AcctNo column is the unique primary index for Table_01. For information about the types of indexes Vantage uses, see Data Distribution and Data Access Methods.

   1. SELECT * FROM Table_01 WHERE AcctNo = 129317 ;
   2. SELECT * FROM Table_01 WHERE AcctBal > 1000 ;
In this example:
  • PEs 1 and 2 receive requests 1 and 2.
  • The data for account 129317 is in table row R9 and stored on AMP1.
  • Information about all account balances is distributed evenly among the disks of all four AMPs.
The sample Teradata SQL statement is processed in the following sequence:
  1. PE 1 determines that the request is a primary index retrieval, which calls for the access and return of one specific row.
  2. The Dispatcher in PE 1 issues a message to the BYNET with an appropriate read step and R9/AMP 1 routing information.
  3. After AMP 1 returns the desired row, PE 1 transmits the data to the client.
  4. The PE 2 Parser determines that this is an all-AMPs request and issues a message to the BYNET with the appropriate read step to broadcast to all four AMPs.
  5. After the AMPs return the results, PE 2 transmits the data to the client.
AMP steps are processed in the following sequence:
  1. Lock—Serializes access where concurrent access would compromise data consistency.

    For some simple requests using Unique Primary Index (UPI), Nonunique Primary Index (NUPI), or Unique Secondary Index (USI) access, the lock step is incorporated into step 2. For information about indexes and their uses, see Data Distribution and Data Access Methods.

  2. Operation—Performs the requested task. Complicated query may have hundreds of operation steps.
  3. End transaction—Releases the locks acquired in step 1 or 2 and tells all AMPs that worked on the request that processing is complete.