Deep Dives — Index
One file per command in the appendix. Each follows the same shape: what it is and when → full syntax → worked examples on the course schema → what breaks at scale → pitfalls → related. Length scales with the command.
38 deep dives in 10 chapters
Chapter 1 — SQL First
-
MERGE
Upsert/delete in one join-driven pass;
ORA-30926means deduplicate the source. -
INSERT SELECT
Set-based copy;
INSERT FIRST/ALL, CTAS,LOG ERRORS; list the columns. -
UPDATE WHERE EXISTS
Correlated update; the
WHERE EXISTSstops NULLs; updatable join view vsMERGE. -
EXISTS
Semi/anti-join that stops at the first row; the
NOT INNULL trap; PL/SQL idioms. -
FETCH FIRST
Top-N with stopkey;
OFFSETis a trap; useROWNUMwithFOR UPDATE.
Chapter 2 — Bulk
-
BULK COLLECT
Many rows per context switch; four forms; no
NO_DATA_FOUND; PGA is the limit. -
LIMIT
Batch size = memory promise; the
%NOTFOUNDbug; 1K–10K is the range. -
FORALL
One DML with a collection attached;
INDICES OF/VALUES OF;APPEND_VALUES. -
SAVE EXCEPTIONS
Bad rows cost one row, not the batch;
ORA-24381; retry singles for real messages. -
CURSOR FOR LOOP
Implicit 100-row prefetch: fine for reads, still row-by-row for DML.
Chapter 3 — Functions Inside SQL
-
DETERMINISTIC
A promise Oracle trusts: function-based indexes, call elimination; never on table reads.
-
RESULT_CACHE
SGA-shared memoization with auto-invalidation; small stable lookups only.
-
PRAGMA UDF
Cheaper SQL→PL/SQL switch (2–4x); inline SQL is still 10–100x.
-
WITH FUNCTION
Query-local PL/SQL, UDF-compiled;
/terminator;WITH_PLSQLhint in DML.
Chapter 4 — In-Memory Lookup
-
ASSOCIATIVE ARRAY
Session hash map for lookups; memory per element × sessions; package-level leaks.
-
COLLECTION METHODS
COUNT≠LAST;FIRST/NEXTfor sparse;DELETEto free;EXTENDfor nested tables. -
NOCOPY
By-reference hint for
OUT/IN OUTcollections; changes exception semantics.
Chapter 5 — COMMIT Strategy
Chapter 6 — Dynamic SQL
-
EXECUTE IMMEDIATE
Values bind, identifiers
DBMS_ASSERT; positional binds; DDL commits. -
OPEN FOR
Ref cursors: dynamic +
BULK COLLECT LIMIT, result sets to clients; always close. -
DBMS_ASSERT
Validators for spliced identifiers;
SQL_OBJECT_NAMEvsSIMPLE_SQL_NAME; whitelist too. -
DBMS_SQL
The lifecycle as calls; unknown columns/binds; cursor reuse;
TO_REFCURSOR.
Chapter 7 — Exceptions
-
EXCEPTION
Expected → specific handler; else log and
RAISE;NO_DATA_FOUNDswallowed in SQL. -
RAISE_APPLICATION_ERROR
Business errors with
-20xxxcodes;keep_errors; numbers in one package. -
PRAGMA EXCEPTION_INIT
Name any ORA error; the list worth naming in batch jobs; deadlock retry.
-
FORMAT_ERROR_BACKTRACE
The line number — only in the first handler;
UTL_CALL_STACK;$$PLSQL_LINE. -
PRAGMA AUTONOMOUS_TRANSACTION
Independent transaction for logs; cannot see the caller's work; deadlocks against it.
Chapter 8 — Locking
-
FOR UPDATE
Lock duration, not count;
SKIP LOCKEDqueues;NOWAIT/WAIT n; optimistic alternative.
Chapter 9 — Huge Tables
-
APPEND HINT
Direct path above the HWM; silent fallback on triggers/FKs;
NOLOGGING+ backup. -
PARALLEL DML
ENABLE PARALLEL DMLor the write stays serial; read the plan; PX servers are shared. -
DBMS_PARALLEL_EXECUTE
Chunks as scheduler jobs, commit per chunk,
RESUME_TASK; rowid vs number-column chunks. -
TRUNCATE
DDL: HWM reset, implicit commit, no rollback; partition truncate instead of
DELETE. -
EXCHANGE PARTITION
Segment swap in a millisecond, in or out;
FOR EXCHANGE WITH; global indexes are the cost.
Chapter 10 — Measure
-
DBMS_APPLICATION_INFO
Module/action on every job step; ASH by step;
SET_SESSION_LONGOPSprogress. -
DBMS_UTILITY GET_TIME
Elapsed vs CPU decides busy vs waiting; stopwatch package; compare logical reads, not seconds.
-
DBMS_HPROF
Hierarchical profiler: which function, who called it, how much is SQL;
plshprofdiff. -
DBMS_XPLAN
The plan that actually ran;
ALLSTATS LAST; E-Rows × Starts vs A-Rows; capture plans from PL/SQL.