Teradata Teradata Performance Tuning 2 — Questions and Answers
Question 1: What is Partition Primary Index (PPI) in Teradata and how does it improve performance?
- Partitions a table by a range of values to enable partition elimination during queries (Correct answer)
- Creates multiple Primary Indexes on a single table
- Distributes a table across multiple databases
- Compresses rows within each partition
Correct 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.
Question 2: In Teradata, what is a spool space and what happens when it runs out?
- Temporary disk space for intermediate query results; query fails with a spool space error (Correct answer)
- Permanent storage reserved for archive tables
- Cache memory for parsed query plans
- Disk space reserved for journal segments
Correct 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.
Question 3: Which Teradata feature allows multiple range conditions on different columns to be combined for partition elimination?
- Multi-Level Partitioning (MLPPI) (Correct answer)
- Compound PPI
- Secondary Partitioning
- Column Partition Bloom Filter
Correct 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.
Question 4: What is the difference between a volatile table and a derived table in Teradata?
- Volatile tables are materialized on disk and persist for the session; derived tables exist only in the query plan (Correct answer)
- Volatile tables exist only in memory; derived tables are written to spool
- Volatile tables are permanent; derived tables are temporary
- Both are identical; only the syntax differs
Correct 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.
Question 5: What does the Workload Management (TASM/TDWM) feature do in Teradata?
- Classifies queries into workloads and allocates resources based on priority and throttle rules (Correct answer)
- Automatically tunes Primary Index selection
- Manages backup scheduling and restore operations
- Balances data distribution across AMPs dynamically
Correct 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.
Question 6: What is a Global Temporary Table in Teradata, and how does it differ from a Volatile Temporary Table?
- A Global Temporary Table's definition persists in the Data Dictionary; data is session-specific, unlike a Volatile Table which has no persistent definition (Correct answer)
- A Global Temporary Table is visible to all users; Volatile Table data is private to the creating session
- Global Temporary Tables are stored in perm space; Volatile Tables use spool
- Both are identical except Global ones support fallback
Correct 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.
Teradata Teradata Performance Tuning 2
What is Partition Primary Index (PPI) in Teradata and how does it improve performance?