Teradata Performance Tuning Flashcards

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

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

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.

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

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

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.

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

Answer: Multi-Level Partitioning (MLPPI)

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

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

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

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.

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

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

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

What is a Global Temporary Table in Teradata, and how does it differ from a Volatile Temporary Table?

Answer: A Global Temporary Table's definition persists in the Data Dictionary; data is session-specific, unlike a Volatile Table which has no persistent definition

The definition of a Global Temporary Table is stored in the Data Dictionary and shared across sessions, but each session has its own private data instance that is deleted at session end.