Teradata Performance Tuning Flashcards

What does the EXPLAIN command do in Teradata?

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.

What is a skewed table in Teradata and why is it a performance problem?

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.

Which Teradata join strategy redistributes both tables by hash value before joining?

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.

What is the purpose of COLLECT STATISTICS in Teradata?

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.

What is a Product Join in Teradata and when should it be avoided?

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.

How does a Join Index improve query performance in Teradata?

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.