Teradata Database Utilities and Architecture

Each AMP is assigned a specific portion of every table's rows through hashing. This distributed architecture allows parallel processing across all AMPs.

NUPI can cause data skew because all rows with the same index value hash to the same AMP. If many rows share one NUPI value, that AMP becomes overloaded while others may be underutilized.

During system reconfiguration (adding/removing AMPs), Teradata creates a reconfiguration map that guides data redistribution. This map helps DBAs understand how data moves between AMPs.

In Teradata, rows with the same Non-Unique Primary Index (NUPI) value generate the same row hash and are sent to the same AMP. Different values hashing to the same row hash are called hash synonyms.

The Parsing Engine consists of three main components: Parser (interprets SQL), Optimizer (generates execution plan), and Dispatcher (coordinates execution). AMPs are separate processors and BYNET is the communication network.

Each AMP is associated with exactly one VDisk, which provides the physical storage for that AMP's data portion. This one-to-one relationship is fundamental to Teradata architecture.

NUPI access goes to a single AMP because all rows with the same index value hash to the same location. This enables efficient single-AMP queries when filtering by the indexed column.

A Primary Key constraint enforces two database integrity rules: every value in the primary key column(s) must be unique, and no value in the primary key column(s) can be null.

Multiload, BTEQ, and Teradata Warehouse Builder support both data export and import operations. Fastload is import-only for empty tables, and TPump is designed for continuous loading.

Multiload is a Teradata utility that can load data into already-populated (existing) tables. It supports both insert and update operations, making it suitable for adding data to tables that already contain records.

Teradata FastLoad is optimized to load large volumes of data into a single, empty target table. It cannot load populated tables or tables with secondary indexes (SI) or referential integrity.

A column defined as NUMBER(10,2) can hold up to 10 significant digits, with 2 of those to the right of the decimal point (leaving 8 digits for the integer part). '871039453.1' has 9 integer digits, causing an overflow error.

FASTLOAD, MULTILOAD, and TPUMP are Teradata utilities with error capture mechanisms. FASTLOAD captures errors in error tables, MULTILOAD has error tables for each phase, and TPUMP logs errors. BTEQ and FASTEXPORT don't have structured error capture.

Teradata MultiLoad (MLoad) executes utility tasks in exactly five distinct phases: Preliminary, DML Modeling, Acquisition, Application, and Transition.

A subquery cannot contain an ORDER BY clause because subqueries are used to define a set of data, and sorting is only meaningful for the final presentation of the main query's output.

BTEQ (Basic Teradata Query) is the only utility that supports all DML operations (SELECT, INSERT, UPDATE, DELETE) with full SQL capabilities. MultiLoad and FastLoad are specialized load utilities optimized for high-volume data loading with limited DML support. TPump supports streaming updates but has restricted DML functionality. BTEQ's interactive SQL nature makes it the most comprehensive for complete DML operations.

The LENGTH function in Oracle/SQL counts the number of characters in a string expression, including special characters and signs. For LENGTH(-1), the function treats '-1' as a two-character string (minus sign and digit 1), not as a numeric value. The output is 2, not 1 (the digit count) or 0. This demonstrates that LENGTH operates on string representation, not mathematical values.

BTEQ supports conditional logic through CASE expressions and WHERE clauses. TPump includes conditional APPLY clauses for row-level logic. MultiLoad supports conditional logic in its APPLY statements with CASE expressions. FASTLOAD is designed strictly for high-speed loading of empty tables and does NOT support conditional logic - it loads all rows without filtering or conditional processing, making it the only incorrect option.

TPUMP (Teradata Parallel Data Pump) is designed for continuous, near-real-time updates and handles frequent, low-volume insert, update, and delete operations with minimal locking.

COUNT(column_name) explicitly ignores NULL values in the specified column. With 14 total rows and one NULL in MGR, COUNT(mgr) returns 13 (not 14). This differs from COUNT(*) which counts all rows regardless of NULL values. Option A correctly states the result is less than 14, though the precise value is 13. The question tests understanding of aggregate function behavior with NULL values.