- 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 – PIVOT and UNPIVOT
Rows into columns and back again. The implicit GROUP BY over every column you did not mention — the one fact behind almost every surprising PIVOT result — plus why values missing from the IN list vanish silently, why an empty cell is NULL from sum but 0 from count, the three errors you get for a non-constant IN list and what to do instead, and UNPIVOT's habit of dropping NULLs by default.
- 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.