At a large car manufacturer, huge volumes of diagnostic data for cars are collected in the following table: https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/TERADATA_TDVAN5/page_8_img_1.jpg The master data for each car is stored in the following table: https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/TERADATA_TDVAN5/page_8_img_2.jpg Many reports require data from both tables by joining via column VehicleId. A very frequently performed query on the system returns the number of events by FaultCode and ModelType. This query consumes many CPU and I/O resources each day. Which action should the Administrator take to improve the runtime and resource consumption for this query?

A. Use an aggregate join index with columns FaultCode, ModelType, as well as an appropriate aggregate function.

Question 1 - Teradata TDVAN5 Real Exam Questions [Jan 2026 Update]

Q: 1

At a large car manufacturer, huge volumes of diagnostic data for cars are collected in the following table: The master data for each car is stored in the following table: Many reports require data from both tables by joining via column VehicleId. A very frequently performed query on the system returns the number of events by FaultCode and ModelType. This query consumes many CPU and I/O resources each day. Which action should the Administrator take to improve the runtime and resource consumption for this query?

Options

Correct Answer:

Explanation

The query frequently joins a large fact table (VehicleEvent) with a master data table (Vehicle) and performs an aggregation (COUNT) grouped by columns from both tables. An Aggregate Join Index (AJI) is the optimal solution for this specific use case. An AJI pre-computes and stores the results of this join and aggregation. When the query is executed, the Teradata Optimizer can directly access the much smaller, pre-aggregated AJI instead of performing the expensive join and aggregation on the large base tables. This dramatically improves query runtime and reduces I/O and CPU resource consumption.

Why Incorrect

B. A sparse join index is designed to index a filtered subset of rows using a WHERE clause, not to pre-compute aggregations.

C. While NUSIs can aid in row lookups, they do not pre-aggregate data. The resource-intensive join and aggregation would still be required.

D. Single-table join indexes provide an alternative primary index for a table but do not pre-join or pre-aggregate data from multiple tables.

References

1. Teradata Vantage™ - Database Design, B035-1094-0123K, Release 17.20, Chapter 10: Join and Hash Indexes, Section: "Aggregate Join Indexes": This document states, "Aggregate join indexes can be used to satisfy queries that summarize large amounts of data... The Optimizer can use an aggregate join index to satisfy a query if the columns in the SELECT and WHERE clauses of the query are a subset of the columns in the index definition, and if the query aggregates are the same as or can be derived from the aggregates in the index definition." This directly matches the scenario.

2. Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184-0423K, Release 17.20, Chapter 12: Join and Hash Indexes, Section: "CREATE JOIN INDEX": The documentation explains, "The primary purpose of an aggregate join index is to enhance the performance of decision support queries that frequently perform the same aggregations on the same column or columns." This confirms that an AJI is the correct tool for a frequently performed aggregate query.

Premium Access Includes

FLASH OFFER

avail 10% DISCOUNT on YOUR PURCHASE