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

Displays table statistics

Answer

Shows index definitions for a table

Answer

Lists active sessions and their queries

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

A table with columns stored in a non-sequential order

Answer

A table that lacks a Secondary Index

Answer

A table where fallback is disabled

Question 3

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

Accepted Answer

Redistribution Join (Hash Join)

Answer

Duplication Join

Answer

Product Join

Answer

Merge Join

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

Rebuilds all indexes on a table

Answer

Compresses table data to reduce disk usage

Answer

Copies table structure to a staging area

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 join optimized for joining tables with the same PI

Answer

A hash-based join between a fact and dimension table

Answer

A join that duplicates the smaller table to every AMP

Question 6

How does a Join Index improve query performance in Teradata?

Accepted Answer

Pre-joins and pre-aggregates data so the optimizer can skip the join at query time

Answer

Stores row hashes for faster PI lookups

Answer

Compresses join columns to reduce I/O

Answer

Partitions both tables by the join key automatically