5Performance design & tuning

• 5.1Index design

  Covers when to use a B-tree index for fast lookups, a hash index for equality search, and a bitmap index for low-selectivity columns; the relationship between column order in a composite index and selectivity; and the trade-off an index imposes on update performance.

• 5.2Execution plans & the optimizer

  Covers how a DBMS automatically chooses how to execute a query via cost-based optimization, the statistics it relies on to judge this, the choice among join methods (nested loop join, sort-merge join, hash join), and how to judge whether a full table scan or an index scan is more advantageous.

• 5.3Tuning & capacity design

  Covers denormalization, intentionally introducing redundancy for performance; partitioning, splitting a large table; SQL tuning, revisiting the SQL statement itself; and bottleneck diagnosis—isolating the true cause of performance degradation and identifying the cause of a slow query.