Explain Indexes and Clustering in IBM Mainframes

42 / 60

DB2intermediate

Indexes and Clustering

Understand how DB2 indexes accelerate access, why clustering indexes matter for physical row order, and the tradeoffs of adding indexes.

An index is a B-tree mapping key values to row locations, so DB2 can find matching rows without reading the whole table space.

A non-clustering index is the index at the back of a textbook pointing you to scattered page numbers for a topic; a clustering index is more like actually reorganizing the whole book so all the pages about that topic sit physically next to each other — faster to read through in bulk, but every time you insert a new page you have to carefully re-sort the whole section rather than just adding one line to the back index.

Key Concepts

The clustering index is different from the others. DB2 tries to keep the rows themselves in that index's key order.

    clustering index on ORDER_DATE
    -> rows for the same date sit on the same pages, in order

    WHERE ORDER_DATE BETWEEN ... AND ...
    -> reads consecutive pages, sequentially

Without clustering, the same range query finds its rows scattered across the table space and reads a different page for nearly every one. On a large table that is the difference between a sequential scan of a few hundred pages and a few thousand random I/Os.

Only one index per table can be the clustering one, because there is only one physical order. So the choice is a design decision made up front: cluster on what your range queries and batch processing use.

Clustering also decays. Inserts go where there is free space rather than where the order says, so a heavily inserted table drifts out of clustering order — which is what REORG restores.

REORG

Every extra index costs writes.

    1 index    an INSERT writes the row, and 1 index entry
    5 indexes  an INSERT writes the row, and 5 index entries

An UPDATE maintains every index on a column it changes. A DELETE removes every entry. On a write-heavy table, five indexes make writes several times the work.

UPDATEDELETE

That is the trade to state explicitly: faster targeted reads, slower writes, more storage. An index nothing queries is pure loss.

Composite indexes and column order. The leading column rules:

    index on (STATUS, OPEN_DATE)

    WHERE STATUS = 'O'                        matches 1 column
    WHERE STATUS = 'O' AND OPEN_DATE > ...    matches 2
    WHERE OPEN_DATE > ...                     matches 0 -- unusable

The index is sorted by status first, so without a status value there is no block to start from. This is the same rule as any B-tree index, and EXPLAIN's MATCHCOLS is where you see how many columns actually matched.

EXPLAINMATCHCOLS

Which means an index appearing in the plan is not enough. ACCESSTYPE = I with MATCHCOLS = 1 on a three-column index means the first column narrowed the search and the rest were checked row by row. That looks like success in the plan and performs like a partial scan.

ACCESSTYPE = IMATCHCOLS = 1

What the interviewer is probing.1. "What does the clustering index control?" Probing: the key concept. Stalls: "Order." Moves up: the physical order rows are kept in, so a range read on that key is sequential rather than scattered.

2. "How many clustering indexes can a table have?" Probing: the obvious follow-up. Stalls: "Several." Moves up: one — which is why choosing it is the single most consequential physical design decision.

3. "What does index-only access mean?" Probing: the optimisation. Stalls: "Using an index." Moves up: every column the query needs is in the index, so the data pages are never touched.

4. "What is the cost of adding an index?" Probing: the trade. Stalls: "None." Moves up: every insert, delete and key update maintains it, plus utility time and storage — so indexes follow queries, not guesses.