There is a call center application that repetitively sends tactical queries to Vantage These queries use a few small tables that are joined on the…

The scenario describes tactical queries joining a few small tables on their primary index (PI). The most effective optimization for this workload is to eliminate all data movement and communication between AMPs during query execution.

Assigning the tables to TD1AmpSparseMap1Node forces all rows of these tables onto a single AMP. Using the same colocation name for all tables is crucial; it informs the Teradata Optimizer that rows with the same PI value across these tables are physically stored together on that single AMP. This allows for a direct, highly efficient local join on one AMP, completely avoiding data redistribution steps and network overhead, which is ideal for the sub-second response times required by tactical queries.

A. Using different colocation names would prevent the optimizer from recognizing that the tables are co-located, negating the benefit of the single-AMP map for PI joins.

C. TDMap1 is a contiguous map that distributes data across a subset of AMPs. This is less optimal than a single-AMP map, as it may still require inter-AMP communication for joins.

D. This option combines a suboptimal map (TDMap1) with incorrect colocation (different names), resulting in an inefficient data layout for the described queries.

1. Teradata Vantage™ - Database Design, 17.20 (B035-1094-172K), Chapter 10: Data Distribution and Maps, Section: "Sparse Maps". This section states, "A sparse map can be used to place a very small table on one or a few AMPs... This is useful for placing small, related tables that are frequently joined on a single AMP to optimize tactical queries."

2. Teradata Vantage™ - Database Design, 17.20 (B035-1094-172K), Chapter 10: Data Distribution and Maps, Section: "Colocating Tables". This section explains, "To colocate tables, you must assign them to the same map and specify the same colocation name... Colocation assures the Optimizer that rows from each table with the same primary index value are on the same AMP."

3. Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, 17.20 (B035-1144-172K), Chapter 2: CREATE TABLE, Section: "Table-level Attributes". The syntax MAP = mapname, COLOCATE USING colocationname is detailed, showing how to implement this strategy. The documentation notes that colocation is a performance feature for PI-based joins.