Queries and AMPs | SQL Fundamentals | VantageCloud Lake - Queries and AMPs - Teradata VantageCloud Lake

An SQL query, which includes DELETE, INSERT, MERGE, and UPDATE and SELECT, can affect one AMP, multiple AMPs, or all AMPs in the configuration.

The SELECT statements in subsequent examples reference the following table data.

The meanings of the abbreviations are as follows.

Single AMP Request

Assume that a PE gets the following SELECT statement:

SELECT last_name
FROM Employee
WHERE employee_number = 1008;

Because a unique primary index value is used as the search condition (the column employee_number is the primary index for the Employee table), PE1 generates a single AMP step requesting the row for employee 1008. The AMP step, with the PE identification, is put into a message, and sent using the BYNET to the relevant AMP (processor).

Flow Diagram of a Single AMP Request

Only one BYNET is shown to simplify the illustration.

As shown in the next diagram, AMP2 retrieves the row from disk, includes the row and the PE identification in a return message, and sends the message back to PE1 using the BYNET. PE1 accepts the message and returns the response row to the requesting application.

Flow Diagram of a Single AMP Response to Requesting PE

The following diagram shows a single AMP request with partition elimination.

All AMP Request

Assume PE1 gets a SELECT statement that specifies a range of primary index values as a search condition as shown in the following example:

SELECT last_name, employee_number
FROM employee
WHERE employee_number BETWEEEN 1001 AND 1010
ORDER BY last_name;

Each value hashes differently and all AMPs must search for the qualifying rows.

PE1 then builds a message for each AMP step and puts that message onto the BYNET. Typically, each AMP step is completed before the next one begins. However queries may generate parallel steps.

When PE1 puts the message for the first AMP step on the BYNET, that message is broadcast to all processors as the following diagram shows.

Partition Elimination

A PPI can increase query efficiency through partition elimination, where partitions that cannot contain qualifying rows can be skipped.

The database supports the following types of partition elimination:

Single AMP Request with Partition Elimination

If a SELECT specifies values for all the primary index columns, the AMP where the rows reside can be determined and only a single AMP is accessed.

If conditions are also specified on the partitioning columns, partition elimination may reduce the number of partitions to be probed on that AMP.

Suppose the Employee table is defined with a single-level PPI where the partitioning column is dept_number.

Assume that a PE gets the following SELECT statement:

SELECT last_name
FROM Employee
WHERE employee_number = 1023
AND dept_number = 501;

The following flow diagram shows this process.

The AMP Step includes the list of partitions (P3) to access. Partition elimination (static partition elimination) reduces access to the partitions that satisfy the query requirements. In each partition in the list (only P3), look for rows with a given row hash value of the PI.

Partition elimination is similar for the Employee table with a multilevel PPI where one partitioning expression uses the dept_number column and another partitioning expression uses the hire_date column.

Assume that a PE gets the following SELECT statement:

SELECT last_name
FROM Employee
WHERE employee_number = 1023
AND dept_number = 501
AND hire_date BETWEEN DATE '2006-01-01' AND DATE '2006-12-31'

The following flow diagram shows this process.

No one was hired in department number 401 in 2006, so partition P4 is empty.

Related Information

See Partitioned and Nonpartitioned Primary Indexes .