Teradata Teradata Performance Tuning 1 — Questions and Answers
Question 1: What does the EXPLAIN command do in Teradata?
- Returns the optimizer's execution plan without running the query (Correct answer)
- Displays table statistics
- Shows index definitions for a table
- Lists active sessions and their queries
Correct answer: Returns the optimizer's execution plan without running the query
EXPLAIN produces a human-readable description of the execution steps the optimizer plans to use, including join strategies and data movement.
Question 2: What is a skewed table in Teradata and why is it a performance problem?
- A table where most rows hash to only a few AMPs, causing uneven workload (Correct answer)
- A table with columns stored in a non-sequential order
- A table that lacks a Secondary Index
- A table where fallback is disabled
Correct answer: A table where most rows hash to only a few AMPs, causing uneven workload
Skew occurs when a poorly chosen Primary Index causes most data to land on a small number of AMPs, leaving others idle and creating bottlenecks.
Question 3: Which Teradata join strategy redistributes both tables by hash value before joining?
- Redistribution Join (Hash Join) (Correct answer)
- Duplication Join
- Product Join
- Merge Join
Correct answer: Redistribution Join (Hash Join)
In a redistribution (hash) join, one or both tables are redistributed across AMPs based on a hash of the join column so matching rows land on the same AMP.
Question 4: What is the purpose of COLLECT STATISTICS in Teradata?
- Gathers demographic data about column values to help the optimizer create better plans (Correct answer)
- Rebuilds all indexes on a table
- Compresses table data to reduce disk usage
- Copies table structure to a staging area
Correct answer: Gathers demographic data about column values to help the optimizer create better plans
COLLECT STATISTICS samples column data and stores demographic summaries (histograms) that the optimizer uses to estimate cardinality and choose efficient query plans.
Question 5: What is a Product Join in Teradata and when should it be avoided?
- A join that produces a Cartesian product, used when no join condition exists, and should be avoided on large tables (Correct answer)
- A join optimized for joining tables with the same PI
- A hash-based join between a fact and dimension table
- A join that duplicates the smaller table to every AMP
Correct answer: A join that produces a Cartesian product, used when no join condition exists, and should be avoided on large tables
A Product Join (Cartesian join) combines every row of one table with every row of another; it is extremely resource-intensive and should be avoided unless intentional.
Question 6: How does a Join Index improve query performance in Teradata?
- Pre-joins and pre-aggregates data so the optimizer can skip the join at query time (Correct answer)
- Stores row hashes for faster PI lookups
- Compresses join columns to reduce I/O
- Partitions both tables by the join key automatically
Correct answer: Pre-joins and pre-aggregates data so the optimizer can skip the join at query time
A Join Index is a physical object that stores the pre-joined result of two or more tables, allowing queries to read the index instead of performing the join at runtime.
Teradata Teradata Performance Tuning 1
What does the EXPLAIN command do in Teradata?