Teradata Teradata Performance Tuning 2

Question 1

What is Partition Primary Index (PPI) in Teradata and how does it improve performance?

Accepted Answer

Partitions a table by a range of values to enable partition elimination during queries

Answer

PPI divides table rows into partitions based on a column range; queries with a filter on the partition column can skip entire partitions, reducing I/O significantly.

Question 2

In Teradata, what is a spool space and what happens when it runs out?

Accepted Answer

Temporary disk space for intermediate query results; query fails with a spool space error

Answer

Spool space holds intermediate results during query processing; when a user or query exhausts the allocated spool limit, Teradata aborts the query with a spool space exceeded error.

Question 3

Which Teradata feature allows multiple range conditions on different columns to be combined for partition elimination?

Accepted Answer

Multi-Level Partitioning (MLPPI)

Answer

MLPPI (Multi-Level Partitioned Primary Index) allows up to 62 levels of partitioning, enabling elimination on multiple column ranges simultaneously.

Question 4

What is the difference between a volatile table and a derived table in Teradata?

Accepted Answer

Volatile tables are materialized on disk and persist for the session; derived tables exist only in the query plan

Answer

A volatile table is created and populated as a separate DDL/DML step and survives for the session duration, while a derived table is a subquery defined inline within the main SQL.

Question 5

What does the Workload Management (TASM/TDWM) feature do in Teradata?

Accepted Answer

Classifies queries into workloads and allocates resources based on priority and throttle rules

Answer

TASM (Teradata Active System Management) classifies incoming requests into workload groups and enforces resource allocation, throttling, and priority rules to manage concurrency.

Teradata Practice Test

Teradata Practice Test

Teradata Teradata Performance Tuning 2