Database Management Systems

The MERGE statement (also known as UPSERT) combines INSERT and UPDATE operations into a single command, eliminating the need for separate statements. This improves performance, simplifies code, and is commonly used in data warehousing ETL processes.

Database transactions can end through explicit COMMIT or ROLLBACK commands, implicit commits triggered by DDL or DCL statements, or session termination (user exit or system crash). All three scenarios terminate the current transaction.

Oracle Database uses automatic, implicit locking at the row level by default. Unlike some databases requiring explicit lock management, Oracle handles locking transparently, making manual lock management unnecessary for most operations.

When restarting an MLOAD script after a failure, the Log Table (Logtable) is essential because it contains the restart information and metadata needed to resume the job from where it left off. The log table tracks the progress of the MLOAD operation and stores critical state information. Error tables, UV tables, and work tables support the operation but are not the primary source of restart data.

RDF stands for Resource Description Framework, a standard framework for data interchange on the Web developed by the W3C. It provides a way to describe resources in a structured, machine-readable format using subject-predicate-object triples.

The MERGE statement combines INSERT and UPDATE actions in a single command, avoiding separate DML statements, improving performance, and simplifying data‑warehouse load processes where upserts are common. All listed benefits are accurate, so the “All of the above” choice correctly captures them.

Database transactions end through multiple mechanisms: explicit COMMIT or ROLLBACK commands, implicit ending when a DDL (CREATE, ALTER, DROP) or DCL (GRANT, REVOKE) statement executes (which auto-commits), or when the user exits the tool or a system crash occurs. All four scenarios represent valid transaction termination points.

DTEXEC is the SQL Server Integration Services (SSIS) command-line utility for executing SSIS packages. It allows packages to be run from command prompts, batch files, or scripts.

Partitioned Primary Index (PPI) is defined during the Physical modeling stage. PPI affects how data is physically stored on disk, making it a physical database design decision rather than logical or extended logical.

When deciding whether to store derived data or calculate it on the fly, access frequency is a key factor - frequently accessed data benefits from storage. Change rating (volatility) is equally important - data that changes often is better calculated to avoid stale values. Maximum NULLs and rows per value are not primary considerations for this decision.

A USI (Unique Secondary Index) subtable stores the index value itself, the RowID pointing to the base table row, and the secondary index's own RowID for internal navigation. AMP number buckets are part of the hash distribution mechanism, not stored in USI subtables. Value-ordered NUSIs have different structures than USIs.

TIME data type stores only time of day (hours, minutes, seconds), not the date component. Hiring an employee requires recording the full date (year, month, day). TIMESTAMP includes both date and time, VARCHAR can store date strings, and CLOB stores character large objects. TIME is the only type that cannot store a complete hire date.