Database Concepts: PL/SQL and Teradata

Functions MUST include a RETURN statement because they are designed to compute and return a value. Procedures can execute without returning anything. The RETURN keyword is compulsory in function definitions to specify what value is computed and sent back to the caller.

The trigger code has a syntax error: TO_CHAR(sysdate, DY) should be TO_CHAR(sysdate, 'DY') with quotes around the format mask. Additionally, the comparison should be TO_CHAR(sysdate, 'DY') = 'MON' with quotes around the day value. As written, this will cause a compilation error, so the statement that it 'will error out' is correct.

In many programming languages, such as Pascal or PL/SQL, procedures can contain nested procedure declarations. This allows for helper routines local to the outer procedure scope.

Multiload utility in Teradata has specific limitations on index support. It does NOT support Unique Secondary Index (USI) during the load operation. However, it does support Non-Unique Secondary Index (NUSI). Option A correctly states that Multiload will not support USI.

In Teradata, indexes are automatically maintained by the system itself, not by database administrators. When data is inserted, updated, or deleted, Teradata automatically updates all affected indexes (both primary and secondary) as part of the transaction management. No manual intervention is required.

Secondary Index in Teradata is primarily used to optimize data access - it provides an alternate path to locate rows without full table scans. Secondary Indexes do NOT determine data distribution across AMPs; only the Primary Index controls distribution. Option A correctly identifies the access purpose.

Teradata does NOT require a Primary Index for a table. Tables can be created without a Primary Index (NOPI tables). This is useful for certain scenarios like staging tables or when distribution control isn't needed. Option B correctly states that Primary Index is not mandatory.

Primary Index in Teradata serves a dual purpose - it determines both how rows are distributed across AMPs AND provides the primary mechanism for accessing those rows. When you query using the Primary Index value, Teradata knows exactly which AMP contains the data. Option C correctly states that Primary Index is used for both access and distribution.

Fastload is a high-speed loading utility in Teradata that has specific limitations on index support. Unlike Multiload (which only excludes USI), Fastload does NOT support either USI or NUSI during the initial load. Indexes must be added after the load completes. Option C correctly states that Fastload will not support both types of secondary indexes.

Changing a Primary Index value in an existing row causes the row to be redistributed to a different AMP because the Primary Index determines data distribution. This requires physically moving the row, which has a performance cost. Teradata will delete the row from the original AMP and re-insert it at the new AMP. Option A correctly describes this cost.

Non-Unique Secondary Index (NUSI) allows duplicate values, so a single NUSI value can point to rows on multiple AMPs. When querying via NUSI, Teradata must check all AMPs that might contain matching rows, making it an All-AMP operation. Unlike USI (which typically hits 1-2 AMPs), NUSI requires broader scanning. Option B correctly identifies this as an All-AMP operation.

In PPI (Partitioned Primary Index) tables, rows are first organized by partition number at each AMP, then by hash value within each partition. This two-level sorting (partition then hash) enables efficient partition elimination in queries. Option B correctly states that rows are first sorted by Partition Number.

Teradata requires exactly one Primary Index per table. This PI (which can be single-column or composite) determines how rows are distributed across AMPs. A table cannot have multiple PIs or zero PIs - the system uses this single PI for all data distribution and retrieval operations.

A Unique Secondary Index typically involves a two-AMP operation: one AMP to access the index subtable (which contains the PI value), then a second AMP to retrieve the base row using that PI value. However, if the index row and base row happen to reside on the same AMP, it becomes a single-AMP operation. The exact number depends on row location.

Views cannot be specified in trigger statements because triggers require base table events to fire properly. A view is a derived object that doesn't directly support the event-based nature of triggers. Triggers must be associated with actual tables that generate the events (INSERT, UPDATE, DELETE).

PL/SQL supports function overloading, which allows multiple functions to share the same name within the same block or package scope. The functions must have different parameter signatures (different number, order, or data types of parameters) so the compiler can distinguish which version to invoke based on the arguments provided.

A database trigger definition can contain only a single SQL or PL/SQL statement. While this single statement can be a compound statement (enclosed within a BEGIN and END block), the trigger structure itself syntactically limits the definition to exactly one top-level statement.