As an example, consider the following Teradata SQL requests using a table containing checking account information. The example assumes that AcctNo column is the unique primary index for Table_01. For information about the types of indexes used by Teradata Database, see Data Distribution and Data Access Methods.
1. SELECT * FROM Table_01 WHERE AcctNo = 129317 ; 2. SELECT * FROM Table_01 WHERE AcctBal > 1000 ;
- PEs 1 and 2 receive requests 1 and 2.
- The data for account 129317 is contained 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:
- PE 1 determines that the request is a primary index retrieval, which calls for the access and return of one specific row.
- The Dispatcher in PE 1 issues a message to the BYNET containing an appropriate read step and R9/AMP 1 routing information. After AMP 1 returns the desired row, PE 1 transmits the data to the client.
- The PE 2 Parser determines that this is an all-AMPs request, then issues a message to the BYNET containing the appropriate read step to be broadcast to all four AMPs.
- After the AMPs return the results, PE 2 transmits the data to the client.
AMP steps are processed in the following sequence:
- Lock—Serializes access in situations 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 will be incorporated into step 2. For information about indexes and their uses, see Data Distribution and Data Access Methods.
- Operation—Performs the requested task. For complicated queries, there may be hundreds of operation steps.
- End transaction—Causes the locks acquired in step 1 or 2 to be released.
The end transaction step tells all AMPs that worked on the request that processing is complete.