# Deep Dives — Index

One file per command in the [appendix](../appendix.md). Each follows the same shape: what it is and when → full syntax → worked examples on the [course schema](../course-plan.md#shared-example-schema) → what breaks at scale → pitfalls → related. Length scales with the command.

| Chapter | Command | Deep dive | In one line |
| --- | --- | --- | --- |
| 1 | MERGE | [merge.md](merge.md) | Upsert/delete in one join-driven pass; `ORA-30926` means deduplicate the source. |
| 1 | INSERT SELECT | [insert-select.md](insert-select.md) | Set-based copy; `INSERT FIRST/ALL`, CTAS, `LOG ERRORS`; list the columns. |
| 1 | UPDATE WHERE EXISTS | [update-where-exists.md](update-where-exists.md) | Correlated update; the `WHERE EXISTS` stops NULLs; updatable join view vs `MERGE`. |
| 1 | EXISTS | [exists.md](exists.md) | Semi/anti-join that stops at the first row; the `NOT IN` NULL trap; PL/SQL idioms. |
| 1 | FETCH FIRST | [fetch-first.md](fetch-first.md) | Top-N with stopkey; `OFFSET` is a trap; use `ROWNUM` with `FOR UPDATE`. |
| 2 | BULK COLLECT | [bulk-collect.md](bulk-collect.md) | Many rows per context switch; four forms; no `NO_DATA_FOUND`; PGA is the limit. |
| 2 | LIMIT | [limit.md](limit.md) | Batch size = memory promise; the `%NOTFOUND` bug; 1K–10K is the range. |
| 2 | FORALL | [forall.md](forall.md) | One DML with a collection attached; `INDICES OF`/`VALUES OF`; `APPEND_VALUES`. |
| 2 | SAVE EXCEPTIONS | [save-exceptions.md](save-exceptions.md) | Bad rows cost one row, not the batch; `ORA-24381`; retry singles for real messages. |
| 2 | CURSOR FOR LOOP | [cursor-for-loop.md](cursor-for-loop.md) | Implicit 100-row prefetch: fine for reads, still row-by-row for DML. |
| 3 | DETERMINISTIC | [deterministic.md](deterministic.md) | A promise Oracle trusts: function-based indexes, call elimination; never on table reads. |
| 3 | RESULT_CACHE | [result_cache.md](result_cache.md) | SGA-shared memoization with auto-invalidation; small stable lookups only. |
| 3 | PRAGMA UDF | [pragma-udf.md](pragma-udf.md) | Cheaper SQL→PL/SQL switch (2–4x); inline SQL is still 10–100x. |
| 3 | WITH FUNCTION | [with-function.md](with-function.md) | Query-local PL/SQL, UDF-compiled; `/` terminator; `WITH_PLSQL` hint in DML. |
| 4 | ASSOCIATIVE ARRAY | [associative-array.md](associative-array.md) | Session hash map for lookups; memory per element × sessions; package-level leaks. |
| 4 | COLLECTION METHODS | [collection-methods.md](collection-methods.md) | `COUNT` ≠ `LAST`; `FIRST/NEXT` for sparse; `DELETE` to free; `EXTEND` for nested tables. |
| 4 | NOCOPY | [nocopy.md](nocopy.md) | By-reference hint for `OUT`/`IN OUT` collections; changes exception semantics. |
| 5 | COMMIT | [commit.md](commit.md) | Every commit waits for LGWR; per batch, restartable by design; `ORA-01555`. |
| 5 | SAVEPOINT | [savepoint.md](savepoint.md) | Partial rollback; what Oracle undoes on its own (less than you think). |
| 6 | EXECUTE IMMEDIATE | [execute-immediate.md](execute-immediate.md) | Values bind, identifiers `DBMS_ASSERT`; positional binds; DDL commits. |
| 6 | OPEN FOR | [open-for.md](open-for.md) | Ref cursors: dynamic + `BULK COLLECT LIMIT`, result sets to clients; always close. |
| 6 | DBMS_ASSERT | [dbms_assert.md](dbms_assert.md) | Validators for spliced identifiers; `SQL_OBJECT_NAME` vs `SIMPLE_SQL_NAME`; whitelist too. |
| 6 | DBMS_SQL | [dbms_sql.md](dbms_sql.md) | The lifecycle as calls; unknown columns/binds; cursor reuse; `TO_REFCURSOR`. |
| 7 | EXCEPTION | [exception.md](exception.md) | Expected → specific handler; else log and `RAISE`; `NO_DATA_FOUND` swallowed in SQL. |
| 7 | RAISE_APPLICATION_ERROR | [raise_application_error.md](raise_application_error.md) | Business errors with `-20xxx` codes; `keep_errors`; numbers in one package. |
| 7 | PRAGMA EXCEPTION_INIT | [pragma-exception_init.md](pragma-exception_init.md) | Name any ORA error; the list worth naming in batch jobs; deadlock retry. |
| 7 | FORMAT_ERROR_BACKTRACE | [format_error_backtrace.md](format_error_backtrace.md) | The line number — only in the first handler; `UTL_CALL_STACK`; `$$PLSQL_LINE`. |
| 7 | PRAGMA AUTONOMOUS_TRANSACTION | [pragma-autonomous_transaction.md](pragma-autonomous_transaction.md) | Independent transaction for logs; cannot see the caller's work; deadlocks against it. |
| 8 | FOR UPDATE | [for-update.md](for-update.md) | Lock duration, not count; `SKIP LOCKED` queues; `NOWAIT`/`WAIT n`; optimistic alternative. |
| 9 | APPEND HINT | [append-hint.md](append-hint.md) | Direct path above the HWM; silent fallback on triggers/FKs; `NOLOGGING` + backup. |
| 9 | PARALLEL DML | [parallel-dml.md](parallel-dml.md) | `ENABLE PARALLEL DML` or the write stays serial; read the plan; PX servers are shared. |
| 9 | DBMS_PARALLEL_EXECUTE | [dbms_parallel_execute.md](dbms_parallel_execute.md) | Chunks as scheduler jobs, commit per chunk, `RESUME_TASK`; rowid vs number-column chunks. |
| 9 | TRUNCATE | [truncate.md](truncate.md) | DDL: HWM reset, implicit commit, no rollback; partition truncate instead of `DELETE`. |
| 9 | EXCHANGE PARTITION | [exchange-partition.md](exchange-partition.md) | Segment swap in a millisecond, in or out; `FOR EXCHANGE WITH`; global indexes are the cost. |
| 10 | DBMS_APPLICATION_INFO | [dbms_application_info.md](dbms_application_info.md) | Module/action on every job step; ASH by step; `SET_SESSION_LONGOPS` progress. |
| 10 | DBMS_UTILITY GET_TIME | [dbms_utility-get_time.md](dbms_utility-get_time.md) | Elapsed vs CPU decides busy vs waiting; stopwatch package; compare logical reads, not seconds. |
| 10 | DBMS_HPROF | [dbms_hprof.md](dbms_hprof.md) | Hierarchical profiler: which function, who called it, how much is SQL; `plshprof` diff. |
| 10 | DBMS_XPLAN | [dbms_xplan.md](dbms_xplan.md) | The plan that actually ran; `ALLSTATS LAST`; E-Rows × Starts vs A-Rows; capture plans from PL/SQL. |