SQLMentor // learn db2

Indexes

Difficulty: Intermediate · ~11 min read

Overview

An index is a separate data structure that lets DB2 find rows by column value without scanning the whole table. Think of it as the index in a book: instead of reading every page to find a topic, you flip to the index and jump straight to the right page.

DB2 LUW indexes are B-tree structures. Each level of the tree narrows the search until DB2 finds the exact row (or rows) and reads them from the table.

When indexes help:

  • WHERE col = X (equality lookup)
  • WHERE col BETWEEN A AND B (range scan)
  • ORDER BY col (avoiding a sort)
  • JOIN ... ON a.col = b.col (efficient match)

When they don't:

  • Tables small enough to fit in memory (full scan is faster)
  • Queries that touch most rows (e.g. > ~20% selectivity)
  • Columns wrapped in functions (WHERE UPPER(col) = ...) — unless you create a matching expression index

Indexes speed reads but slow writes — every INSERT/UPDATE/DELETE must keep them in sync.

Syntax

-- Plain B-tree index
CREATE INDEX idx_name ON table (col1, col2);

-- Unique index
CREATE UNIQUE INDEX uniq_name ON table (col);

-- Index on expression
CREATE INDEX idx_upper ON customers (UPPER(email));

-- Index with included (non-key) columns (covering index)
CREATE INDEX idx_cover ON orders (customer_id)
  INCLUDE (amount, ordered_at);

-- Clustering index (rows physically ordered by this index)
CREATE INDEX idx_clust ON orders (ordered_at) CLUSTER;

-- Drop
DROP INDEX idx_name;

Examples

Example 1: Simple index for fast lookups

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email       VARCHAR(254) NOT NULL,
  name        VARCHAR(80),
  signup_date DATE
);
-- Email lookups are common; index it
CREATE UNIQUE INDEX idx_cust_email ON customers (email);

SELECT * FROM customers WHERE email = 'alice@example.com';
-- DB2 uses idx_cust_email → directly reads the matching row

A unique index also enforces uniqueness — duplicate inserts fail.

Example 2: Composite (multi-column) index

CREATE INDEX idx_cust_region_date
  ON customers (region, signup_date);

-- Excellent for:
SELECT * FROM customers
WHERE  region = 'EU' AND signup_date >= '2025-01-01';

-- Also helps:
SELECT * FROM customers WHERE region = 'EU';

-- Does NOT help (skips the leading column):
SELECT * FROM customers WHERE signup_date >= '2025-01-01';

Column order matters! Put the most-filtered column first. A composite index also implicitly indexes any prefix of its key columns.

Example 3: Covering index with INCLUDE

CREATE INDEX idx_orders_cover
  ON orders (customer_id)
  INCLUDE (amount, ordered_at);

-- DB2 can satisfy this query from the index alone — no table access needed
SELECT customer_id, amount, ordered_at
FROM   orders
WHERE  customer_id = 42;

INCLUDE columns aren't part of the key (no sort order, no uniqueness) but they're carried in the index leaf pages. This is the covering index pattern: the index "covers" the query.

Example 4: Expression index

CREATE INDEX idx_cust_email_lower
  ON customers (LOWER(email));

-- Now this is fast (otherwise it would be a full scan)
SELECT * FROM customers WHERE LOWER(email) = 'alice@example.com';

Use expression indexes when your queries always wrap a column in the same function.

Example 5: Clustering index

CREATE INDEX idx_orders_clust
  ON orders (customer_id, ordered_at) CLUSTER;

-- After REORG, rows are stored on disk in customer_id, ordered_at order
REORG TABLE orders;

-- Range scans for one customer are now extremely fast — adjacent rows are on adjacent pages
SELECT * FROM orders WHERE customer_id = 42 ORDER BY ordered_at;

A table can have only one clustering index (the physical row order can only be one thing). REORG rewrites the table in clustering order.

Example 6: Checking what indexes exist

SELECT  indname, tabname, colnames, uniquerule
FROM    syscat.indexes
WHERE   tabschema = CURRENT_USER
ORDER   BY tabname, indname;

SYSCAT.INDEXES is the catalog view; colnames shows the indexed columns.

Example 7: Asking DB2 if an index is being used

EXPLAIN PLAN FOR
SELECT * FROM customers WHERE email = 'alice@example.com';

-- View the plan
SELECT *
FROM TABLE(SYSPROC.EXPLAIN_FORMAT_STATS('PLAN_TABLE', NULL, NULL));

Look for IXSCAN (index scan) versus TBSCAN (table scan). If you see TBSCAN on a frequent query, you probably need an index.

Example 8: Dropping and re-creating

DROP INDEX idx_cust_email;

-- DB2 keeps statistics with the table; rebuild them after big index changes
RUNSTATS ON TABLE customers FOR INDEXES ALL;

Notes & Tips

  • Every PK and UNIQUE constraint auto-creates a unique index. Don't add a duplicate.
  • A composite index on (a, b) helps WHERE a = … and WHERE a = … AND b = … — but not WHERE b = … alone (you'd need a separate index on b).
  • Each extra index costs write performance and storage. Audit SYSCAT.INDEXES periodically and drop unused ones (DB2's db2pd -tcbstats shows usage counts).
  • For OLTP workloads, target ~5–10 indexes per heavily-read table. For data warehouses, far fewer but larger composite indexes are typical.
  • After bulk loads, always RUNSTATS so the optimizer has accurate selectivity. Out-of-date stats are the #1 cause of bad plans.
  • REORG TABLE and REORG INDEXES ALL FOR TABLE defragment and rebuild — schedule during maintenance windows.
  • DB2 supports index-only access: if all columns the query needs are in the index (key + INCLUDE), the table itself is never read. This is the holy grail of read performance.

Practice Exercises

  1. Create a composite index (region, signup_date) on customers. Run EXPLAIN for WHERE region = 'EU' and confirm the index is used.
  2. Add an INCLUDE (name) covering index for email lookups so the table is never read.
  3. Force a table scan by querying WHERE UPPER(email) = … without an expression index — note the plan changes. Add the expression index and re-run.
  4. Set up a clustering index on a date column. Compare EXPLAIN cost for a range scan before and after REORG.
  5. Use SYSCAT.INDEXES to find any table in your schema with more than 5 indexes — those are candidates for review.

Quick Quiz

Q1. Why does WHERE col = 'x' use an index on col, but WHERE UPPER(col) = 'X' usually doesn't?

Show answer

Indexes store column values, not values of arbitrary expressions over the column. When you wrap col in a function, DB2 can't look up UPPER(col) directly in a plain index on col. The fix is an expression index: CREATE INDEX … ON t (UPPER(col)) — the index now stores the upper-cased values and the query can use it.

Q2. What is a "covering" index?

Show answer

An index that contains all the columns the query needs — both for filtering and for the SELECT list. DB2 satisfies the query entirely from the index leaf pages, never touching the table. Add non-filter columns via the INCLUDE clause to avoid bloating the key.

Q3. What is a clustering index?

Show answer

A special index that controls the physical order of rows in the table. When DB2 reorgs the table, rows are stored on disk in clustering-key order. Range scans on the clustering key are then extremely fast because adjacent rows are on adjacent pages. A table can have only one clustering index.

Next Up

We've optimised reads with indexes. Now we move to stored procedures — packaged server-side logic in SQL PL.