Teradata Teradata Performance Tuning

Question 1

What does the EXPLAIN command do in Teradata?

Accepted Answer

Returns the optimizer's execution plan without running the query

Answer

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?

Accepted Answer

A table where most rows hash to only a few AMPs, causing uneven workload

Answer

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?

Accepted Answer

Redistribution Join (Hash Join)

Answer

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?

Accepted Answer

Gathers demographic data about column values to help the optimizer create better plans

Answer

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?

Accepted Answer

A join that produces a Cartesian product, used when no join condition exists, and should be avoided on large tables

Answer

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.

Teradata Practice Test

Teradata Practice Test

Teradata Teradata Performance Tuning