All-AMP Queries and Tactical Queries | Database Design | Teradata Vantage - 17.10 - All-AMP Queries - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update

Some tactical queries require all-AMPs steps. All-AMPs queries are likely to have a more relaxed response time expectation than single-AMP queries even when they are capable, at times of low concurrency, of subsecond response.

Coding Suggestions for All-AMP Tactical Queries

All-AMP tactical queries behave differently than few-AMP queries as the level of concurrency increases. Some suggestions for coding tactical queries that involve all-AMPs operations follow:
  • Review the queries for unnecessary database access. If found, eliminate those accesses from the query.
  • Tune row-partitioned tables so queries against them can avoid unnecessary probing.
  • Rely on NUSI access, where possible, to avoid scanning a large table.
  • Define a hash or join index that partitions the base table vertically where it makes sense to do so because scanning a join index that contains only a subset of the columns from the base table is faster than scanning the base table.
  • Look for possibilities to break complex tactical queries into several smaller statements and encapsulating them within a procedure, particularly when all-AMP operations can be replaced by multiple single-AMP operations.
  • Specify explicit ACCESS locks wherever possible (see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142 for more information) or set the default session read lock to READ UNCOMMITTED using the SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL statement (see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144).

    ACCESS locks are more important for all-AMP queries than for single-AMP queries because an all-AMP query can require access to more data over a longer period than a single-AMP query.