TDVAN5 practice question 8 of 15
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 primary index column. The following maps are defined in the system: * TD_GlobalMap * TD_Map1 * TD_DataDictionaryMap * TD_1AmpSparseMap_1Node What can be done to optimize these queries?
Full set: 72 questionsThe 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.
Keep practicing
Take the free TDVAN5 practice test
Ten exam-style questions with answers and explanations, plus the exam facts and study guides.
More questions
Other TDVAN5 practice questions
- Question 1Which description accurately characterizes the use of external authentication for Vantage?
- Question 2An Administrator needs to provide end users access to data in a Native Object Store using…
- Question 3There is corruption in a table in the DBC database. Which action should the Administrator…
- Question 4A Vantage customer is using the Hive Connector in QueryGrid to access a foreign server an…
- Question 5A Vantage instance includes the following four databases: Tables Database contains base t…
- Question 6Which benefit is achieved by creating profiles?
