Database Management Systems

PMON (Performance Monitor) is a command-line or graphical tool specifically designed to monitor sessions, processes, and resource usage in a Teradata database. SQL Assistant and BTEQ are query execution tools, and Statistics Wizard is used for managing statistics, not for real-time process monitoring.

Teradata is a Massively Parallel Processing (MPP) system designed for data warehousing and analytical processing (OLAP), not transaction processing (OLTP). OLAP involves complex queries on large historical datasets, which is Teradata's strength.

UPI (Unique Primary Index) in Teradata is equivalent to a Primary Key in Oracle - both enforce uniqueness and are used for data distribution. A regular PI (without uniqueness) is not equivalent to a PK.

MultiLoad is designed for loading data into already populated tables (UPDATE/INSERT/DELETE operations). FastLoad is for empty tables only, FastExport is for extracting data, and TPUMP is for real-time trickle feed.

FSLDM (Financial Services Logical Data Model) is Teradata's industry-specific logical data model designed for financial markets and banking institutions. It provides pre-defined schemas for common financial entities like customers, accounts, transactions, and risk management. HCLDM, RLDM, and CLDM are not standard Teradata logical data models.

Teradata MultiLoad supports loading, inserting, updating, or deleting data in up to 5 target tables within a single MultiLoad utility execution job, which helps optimize resource consumption.

Teradata Manager is the administrative tool specifically designed for monitoring, collecting, and analyzing database performance metrics, resource usage, and system health. BTEQ is a query utility, Teradata SQL Assistant is for SQL development, and TDQM is not a standard Teradata performance tool.

Archive/Recovery utility is designed to copy tables (archive) and restore them to another database (recovery). BTEQ is for query processing, Table rebuild is for rebuilding damaged tables, and FastLoad is for high-speed initial data loading into empty tables.

With Partitioned Primary Index (PPI), data is first distributed based on the Primary Index (PI) across AMPs, then partitioned within each AMP based on the partitioning column. The primary distribution mechanism is still the Primary Index hash. Secondary index and primary key don't control distribution in PPI.

MultiLoad is the utility designed for batch processing and block-level loading against already-populated tables. It can handle INSERT, UPDATE, DELETE, and UPSERT operations efficiently. FastLoad is for initial load to empty tables only, TPUMP is for continuous trickle feed, and BulkLoad is not a standard Teradata utility.

The Teradata Index Wizard analyzes a query workload to recommend secondary indexes (USIs and NUSIs) that will improve query performance, rather than primary indexes which are defined during table creation.

Teradata uses a shared-nothing architecture where each node (with its AMPs) has its own memory and disk storage, and doesn't share resources with other nodes. This enables linear scalability. Shared memory (SMP) and shared everything are different architectures where resources are shared across processors.

A Non-Unique Primary Index (NUPI) can group rows with the same index value on the same AMP, which makes query access, aggregations, and joins on those columns highly effective, despite not guaranteeing uniform distribution.

A Unique Secondary Index (USI) value is hashed to determine which AMP contains the index subtable. Since USI enforces uniqueness, Teradata distributes USI rows across exactly 2 AMPs (one for the index, one as backup) to ensure single-AMP access during joins. This is more efficient than all-AMP or multi-AMP access, but more redundancy than 1-AMP.