Using a surrogate key (like a sequence-generated number) decouples the primary key from business data, providing flexibility if business rules (like email uniqueness) change. It’s typically more performant for joins and indexing than a large VARCHAR key. A separate unique constraint on the email still enforces the critical business rule for data integrity.

A standard B-tree index is built on the column’s actual values. When you apply a function like `UPPER()` to the column in the WHERE clause, Oracle cannot perform a direct lookup because the index stores ‘Smith’, not ‘SMITH’. To resolve this, you could create a function-based index on `UPPER(last_name)`.

The `WITH GRANT OPTION` allows the grantee (hr_manager) to become a grantor themselves, passing the specified privileges on to other users. This is a powerful feature that must be used carefully, as it can lead to complex privilege chains that are difficult to manage and revoke.

Range partitioning on a date column is ideal for time-series data. It allows for partition pruning, where queries targeting a specific date range only scan the relevant partition(s). This dramatically speeds up queries on recent data. Furthermore, older partitions can be easily archived, compressed, or moved to slower, cheaper storage, simplifying data lifecycle management.

While AUTOEXTEND allows a datafile to grow, it can only do so if there is physical free space available on the storage device (disk, SAN, etc.) where the file is located. The error indicates the database cannot allocate more space because the OS volume is full. The solution is to add space at the OS level or add a new datafile to the tablespace on a different volume.

The key difference is duplicate handling. `UNION` performs a `SELECT DISTINCT` on the combined results, which requires sorting and unique filtering—a potentially expensive operation. `UNION ALL` simply concatenates all rows from all queries. If you know your data has no duplicates or duplicates are acceptable, `UNION ALL` is significantly more efficient.

A Foreign Key (Referential Integrity) constraint is specifically designed to enforce a parent-child relationship between tables. It guarantees that a value in a column (or set of columns) must exist as a primary or unique key in another table. This prevents “orphaned” orders that reference non-existent customers.

The `EXPLAIN PLAN` command (or using tools like SQL Developer’s Explain Plan feature) shows the execution plan the Oracle optimizer chooses. This plan will clearly indicate access methods like `TABLE ACCESS FULL` (a full scan) or `INDEX RANGE SCAN`. It is the fundamental diagnostic tool for understanding query performance before making changes.

A `BEFORE UPDATE` row-level trigger fires automatically for each row updated, before the change is committed. Inside this trigger, you can access the `:OLD.salary` and `:NEW.salary` values and insert them into a separate audit table. This provides a reliable, code-independent mechanism for tracking changes.

Using a surrogate key (like a sequence-generated number) decouples the primary key from business data, providing flexibility if business rules (like email uniqueness) change. It’s typically more performant for joins and indexing than a large VARCHAR key. A separate unique constraint on the email still enforces the critical business rule for data integrity.0