- Oracle Database – Transactions and Locking
Why readers never block writers, and how undo delivers both rollback and read consistency. Where a transaction starts, the DDL that commits behind your back, the two isolation levels Oracle actually has and the ORA-08177 retry that SERIALIZABLE requires. SELECT FOR UPDATE SKIP LOCKED as a work queue, deadlocks, and finding the blocker.
- Oracle Database – Indexes and Execution Plans
Tuning Oracle is one skill: get the real plan and compare estimated rows against actual rows. Index types and the composite leading-column rule, the four reasons your index is ignored — including implicit conversion and the NULLs a B-tree does not store — DBMS_XPLAN with GATHER_PLAN_STATISTICS, bind variables, and invisible indexes.
- Oracle Database – PL/SQL
Anonymous blocks, %TYPE and %ROWTYPE, the two exceptions SELECT INTO can raise, cursor FOR loops, and packages as the unit of organisation. Exception handling done properly, why WHEN OTHERS THEN NULL is the worst line of code in the Oracle world, BULK COLLECT and FORALL with SAVE EXCEPTIONS — and when to write plain SQL instead.
- Oracle Database – Analytic Functions
Window functions are the feature that most changes how you write SQL. ROW_NUMBER, RANK and DENSE_RANK compared on ties, top-N per group, deduplication by ROWID, LAG and LEAD, running totals — including the ROWS versus RANGE default that quietly gives wrong numbers — the LAST_VALUE frame trap, KEEP DENSE_RANK, and gaps and islands.
- Oracle Database – CROSS APPLY and OUTER APPLY
An inline view that can see the row it is joined to, evaluated once per outer row. The four spellings and why CROSS APPLY silently drops rows where OUTER APPLY keeps them. Top-N per group and exactly when it beats ROW_NUMBER, several aggregates in one pass, per-row table functions, and how to read Starts and WINDOW NOSORT STOPKEY in the plan.