Teradata Performance Tuning Flashcards

Which EXPLAIN plan indicator suggests that no statistics exist for a column used in a join or filter?

Answer: "No confidence" or relying on defaults

When the optimizer lacks statistics, EXPLAIN reports "no confidence" in its cardinality estimates, signaling that COLLECT STATISTICS should be run.

What is the Teradata Index Wizard used for?

Answer: Analyzes a workload and recommends indexes to improve query performance

The Index Wizard examines a set of submitted queries and recommends new secondary indexes, join indexes, or hash indexes to improve overall workload performance.

What is a Hash Index in Teradata and how does it differ from a Join Index?

Answer: A Hash Index provides fast single-AMP access for non-PI columns; a Join Index pre-materializes joined rows

A Hash Index copies and reorganizes a table using a different hash key, enabling single-AMP access on non-PI columns, while a Join Index stores pre-computed join results.

Which Teradata feature compresses repeated column values within a row to reduce storage?

Answer: Multi-Value Compression (MVC)

Multi-Value Compression stores frequently repeated values in the table header rather than in each row, reducing disk usage and improving I/O performance.

What does the DBQL (Database Query Log) in Teradata record?

Answer: Query text, CPU, I/O, and elapsed time for submitted queries for workload analysis

DBQL captures SQL text, resource consumption (CPU/I/O), elapsed time, and session details for queries, enabling performance analysis and workload tuning.

In Teradata, what is the purpose of the FastExport utility in a performance context?

Answer: Extracts large volumes of data from Teradata in parallel to flat files efficiently

FastExport uses multiple parallel sessions to extract large amounts of data from Teradata to external flat files with high throughput, ideal for large data extracts.