Chapter 15: Index Design — B+ Trees, Index-Only Access, Clustering, and the Art of Knowing What to Index

An index is a promise to the optimizer: "I'll help you find what you're looking for, fast." The art is knowing which promises to make — and which to break when they cost more than they deliver.

Learning Objectives

After completing this chapter, you will be able to:

1. Describe the B+ tree structure used by DB2 indexes and trace how DB2 traverses an index to locate data, mapping tree height directly to physical I/O operations.
2. Design indexes that support common query patterns including equality lookups, range scans, and multi-column predicates.
3. Explain clustering indexes and their impact on sequential I/O, random I/O, and prefetch effectiveness.
4. Implement index-only access (covering indexes) to eliminate table access entirely, and recognize when the optimizer will choose this path.
5. Evaluate index trade-offs — read performance improvement versus write overhead, storage cost, and catalog maintenance.
6. Design the Meridian National Bank indexing strategy with explicit rationale for each index created.

15.1 Why Indexes Exist

Imagine you are searching for a single customer in a table with ten million rows. Without an index, DB2 must read every page of the tablespace, examining every row, checking whether it matches your predicate. This is called a tablespace scan (or table scan on LUW). It is the brute-force approach: reliable, always available, and catastrophically expensive when the table is large and you only need one row.

15.1.1 The Scale of the Problem

Let us make the cost concrete. Suppose the CUSTOMER table at Meridian National Bank holds 10 million rows. Each row is 400 bytes. The page size is 4 KB (4,096 bytes). With some overhead for page headers and row overhead, you can fit roughly 9 rows per page. That means the table occupies approximately 1,111,111 pages — about 4.3 GB of data.

To find one customer by CUSTOMER_ID, a tablespace scan must read all 1.1 million pages. Even with sequential prefetch (which reads multiple pages in a single I/O), this takes thousands of I/O operations. At 1 ms per I/O with aggressive prefetch, you are looking at several seconds. Without prefetch — for random single-page reads — the cost would be many minutes.

Now consider an index on CUSTOMER_ID. A B+ tree index on 10 million rows will typically have a height of 3 or 4 levels. To find one customer, DB2 reads 3 or 4 index pages (root, one or two non-leaf pages, one leaf page), then one data page to retrieve the row. That is 4 or 5 I/O operations. If the upper levels of the index are cached in the buffer pool — and they almost always are — the effective cost drops to 1 or 2 I/O operations.

The difference is not 10% or 50%. It is five orders of magnitude. This is why indexes exist.

15.1.2 The Basic Trade-Off

Every index you create imposes costs:

• Storage: The index occupies disk space. A large index on a large table can consume gigabytes.
• Write overhead: Every INSERT must add an entry to every index on the table. Every DELETE must remove entries. Every UPDATE that modifies an indexed column must update every affected index. For a table with five indexes, an INSERT does six writes: one to the table plus one to each index.
• Maintenance: Indexes can become fragmented over time, requiring REORG operations. Index statistics must be gathered and kept current for the optimizer.
• Catalog complexity: The more indexes you have, the harder it is for the optimizer to choose among them, and the harder it is for a human DBA to understand the overall access strategy.

The fundamental question of index design is: Does the read benefit justify the write cost? This question cannot be answered in the abstract. It depends on the workload — the ratio of reads to writes, the specific queries being executed, their frequency, and their performance requirements.

A reporting table that is loaded once nightly and queried thousands of times per day can justify many indexes. An OLTP table that receives 10,000 inserts per second might need only the essential minimum. The art is in knowing the workload and designing accordingly.

15.1.3 Access Paths: Scan vs. Index

DB2's optimizer considers two fundamental categories of access when executing a query:

1. Tablespace scan (z/OS) or table scan (LUW): Read every page of the table. Filter rows that do not match predicates. Effective when you need a large fraction of the table (typically more than 10-20% of rows), or when no suitable index exists.

2. Index access: Use an index to locate specific rows, then fetch only the needed data pages. This takes several forms: - Matching index scan: The query predicates match the leading columns of an index. DB2 traverses the B+ tree to find the starting point and scans leaf pages. - Non-matching index scan: DB2 scans all leaf pages of the index (cheaper than scanning the table if the index is smaller, or if index-only access is possible). - Index-only access: All columns needed by the query are present in the index. DB2 never touches the table at all. - Multiple index access: DB2 uses two or more indexes, combines the qualifying RID (row identifier) lists, and fetches data pages. Available on z/OS as RID Pool processing and on LUW via bitmap index ANDing/ORing.

The optimizer uses catalog statistics — number of rows, number of distinct values, distribution statistics, cluster ratio — to estimate the cost of each access path and choose the cheapest one. This is the cost-based optimization that Patricia Selinger pioneered at IBM, which we explored in Chapter 3. Indexes are the primary lever you have for influencing the optimizer's choices.

15.2 B+ Tree Internals

This section describes a threshold concept: the internal structure of the B+ tree index and how that structure maps directly to physical I/O operations. Once you truly understand this, every other topic in index design — clustering, covering indexes, composite index column order — becomes intuitive.

15.2.1 Why B+ Trees?

DB2 uses B+ trees (a variant of balanced tree data structures) for virtually all of its indexes. The B+ tree was chosen for specific properties that align with database requirements:

• Balanced: Every leaf page is the same distance from the root. This guarantees predictable performance — no worst-case scenarios where one lookup requires ten times more I/O than another.
• Wide and shallow: Each non-leaf page holds many key-pointer pairs (hundreds or thousands), so the tree stays short even for very large tables. A tree of height 3 can index hundreds of millions of rows.
• Leaf-page chaining: Leaf pages are linked together in key order, making range scans efficient. After finding the starting point, DB2 simply follows the chain forward (or backward).
• Efficient for both equality and range lookups: The tree structure supports =, <, >, BETWEEN, and LIKE 'prefix%' predicates naturally.

15.2.2 The Structure of a B+ Tree

A B+ tree has three types of pages:

                        +------------------+
                        |    ROOT PAGE     |
                        | Key1 | Key2 |... |
                        | Ptr0|Ptr1|Ptr2|..|
                        +--+------+-----+--+
                           |      |     |
              +------------+      |     +-----------+
              |                   |                  |
              v                   v                  v
     +------------------+ +------------------+ +------------------+
     |  NON-LEAF PAGE   | |  NON-LEAF PAGE   | |  NON-LEAF PAGE   |
     | Key|Key|Key|...  | | Key|Key|Key|...  | | Key|Key|Key|...  |
     | Ptr|Ptr|Ptr|...  | | Ptr|Ptr|Ptr|...  | | Ptr|Ptr|Ptr|...  |
     +--+----+----+--+--+ +--+----+----+-----+ +--+----+----+-----+
        |    |    |           |    |    |          |    |    |
        v    v    v           v    v    v          v    v    v
     +------+ +------+    +------+ +------+    +------+ +------+
     | LEAF | | LEAF |    | LEAF | | LEAF |    | LEAF | | LEAF |
     | Page | | Page |    | Page | | Page |    | Page | | Page |
     |======| |======|    |======| |======|    |======| |======|
     |K1 RID| |K4 RID|   |K9 RID| |K13RID|   |K19RID| |K23RID|
     |K2 RID| |K5 RID|   |K10RID| |K14RID|   |K20RID| |K24RID|
     |K3 RID| |K6 RID|   |K11RID| |K15RID|   |K21RID| |K25RID|
     |  --> | |  --> |    |  --> | |  --> |    |  --> | |  --> |
     +------+ +------+    +------+ +------+    +------+ +------+
        Linked list of leaf pages (chained left-to-right in key order)

Root page: The single top-level page. It contains key values and pointers to child pages. For a small index, the root page might also be the only leaf page.

Non-leaf pages (also called internal pages or intermediate pages): These contain key values and pointers to child pages at the next level down. Each key acts as a separator, directing the search to the correct subtree. If you are looking for key value K, and the non-leaf page has separator keys S1 < S2 < S3, then: - If K < S1, follow pointer P0 (leftmost child). - If S1 <= K < S2, follow pointer P1. - If S2 <= K < S3, follow pointer P2. - If K >= S3, follow pointer P3.

Leaf pages: The bottom level. Each leaf page entry contains: - The full key value (the indexed column values). - A RID (Row Identifier) pointing to the actual data row in the table. On z/OS, the RID encodes the page number and row slot within the page. On LUW, it is similar (a combination of page number and slot number within the page). - Optionally, INCLUDE columns — additional non-key data stored in the leaf entry for index-only access.

Leaf pages are linked to their neighbors in key order by forward and backward pointers. This doubly linked list is the mechanism that makes range scans efficient.

15.2.3 The Search Algorithm

To find a row with key value K, DB2 performs the following:

1. Read the root page. This is almost certainly in the buffer pool (it is the most frequently accessed page in the index).
2. Binary search within the root page to find the correct pointer to the next level.
3. Read the non-leaf page. If the tree has height 3, there is one non-leaf level. If height 4, there are two non-leaf levels, and this step repeats.
4. Binary search within the non-leaf page to find the correct pointer to the leaf page.
5. Read the leaf page. Binary search within it to find the entry for key K.
6. Extract the RID from the leaf entry.
7. Read the data page using the RID to fetch the actual row.

For an equality lookup on a unique index, this is exactly one traversal: root to leaf to data page. For a range scan (e.g., WHERE CUSTOMER_ID BETWEEN 1000 AND 2000), DB2 finds the starting leaf entry and then follows the leaf-page chain forward, collecting RIDs as it goes, until it passes the end of the range.

15.2.4 Tree Height and Its Relationship to I/O

The height of the B+ tree is the critical determinant of index lookup cost. Here is why B+ trees stay remarkably short.

Suppose each page is 4 KB. A non-leaf entry consists of a key value (say 8 bytes for a BIGINT) plus a page pointer (typically 4 bytes), plus some overhead — roughly 16 bytes per entry. A 4 KB page can hold approximately 250 non-leaf entries (accounting for page header and slot directory overhead).

A leaf entry is larger because it includes the full key plus the RID (and possibly INCLUDE columns). Suppose 40 bytes per leaf entry; a 4 KB page holds about 90 leaf entries.

With these numbers:

A B+ tree of height 3 can index over 5 million rows. Height 4 handles over a billion rows. This means that any single-row lookup via index requires at most 4 index page reads plus 1 data page read. In practice, the root page and often the first level of non-leaf pages reside permanently in the buffer pool, so the physical I/O cost is typically 1-2 page reads for the index plus 1 for the data page.

This is the insight that makes B+ trees transformative: regardless of whether the table has one million rows or one billion rows, the cost of an indexed lookup grows only logarithmically. Adding a factor of 1,000 to table size adds at most one level to the tree — one additional I/O.

15.2.5 Leaf Page Chaining and Range Scans

When DB2 executes a range scan — a query like WHERE TRANSACTION_DATE BETWEEN '2025-01-01' AND '2025-01-31' — the process is:

1. Traverse the B+ tree to find the leaf page containing the first key in the range (2025-01-01).
2. Read that leaf page and collect qualifying RIDs.
3. Follow the forward pointer to the next leaf page.
4. Repeat until reaching a key value beyond the end of the range (past 2025-01-31).

The beauty of this design is that once DB2 reaches the first qualifying leaf page, it never needs to go back to the root or non-leaf pages. It simply walks the linked list. If the range covers 100 leaf pages, DB2 reads the root (1 I/O), one or two non-leaf pages (1-2 I/O), and then 100 leaf pages sequentially (100 I/O, but highly prefetchable). Total: about 103 I/O operations, regardless of the total tree size.

DB2 can also scan leaf pages in reverse order (following backward pointers) for ORDER BY ... DESC queries or for descending index scans.

15.2.6 Page Splits and Free Space

When a new entry must be inserted into a leaf page that is full, DB2 performs a page split: it allocates a new leaf page, moves approximately half the entries to the new page, inserts the new entry, and updates the parent non-leaf page and the leaf-page chain pointers. Page splits are expensive because they involve multiple page writes and can cause index fragmentation.

The PCTFREE parameter on an index controls how much free space is left on each page during index creation or REORG. A higher PCTFREE reduces the frequency of page splits (because pages have room for new entries) but increases the size of the index.

For tables with monotonically increasing keys (like auto-generated IDs or timestamps), new entries always go to the rightmost leaf page, and page splits occur only at the right edge of the index. This pattern is relatively benign. For randomly distributed keys (like UUIDs or hash values), inserts can hit any leaf page, causing splits throughout the index. This is one reason why auto-incrementing integer keys are generally preferred for primary keys in OLTP systems.

15.3 Creating Indexes in DB2

15.3.1 Basic Syntax

The fundamental CREATE INDEX statement is straightforward:

-- Basic unique index
CREATE UNIQUE INDEX IX_CUSTOMER_PK
    ON CUSTOMER (CUSTOMER_ID);

-- Non-unique index
CREATE INDEX IX_CUSTOMER_LAST_NAME
    ON CUSTOMER (LAST_NAME);

When you define a PRIMARY KEY or UNIQUE constraint on a table, DB2 automatically creates a unique index to enforce it. You can also explicitly create the index first and then define the constraint to use it — this gives you more control over the index properties.

15.3.2 Ascending and Descending Key Order

By default, index keys are stored in ascending order. You can specify descending order for any column:

-- Index for queries that sort by date descending
CREATE INDEX IX_TRANSACTION_DATE_DESC
    ON TRANSACTION (TRANSACTION_DATE DESC);

This matters for ORDER BY performance. If a query requests ORDER BY TRANSACTION_DATE DESC, an ascending index can still be used (DB2 scans the leaf pages in reverse via backward pointers), but a descending index allows forward scanning, which can be slightly more efficient for prefetch.

For composite indexes, you can mix orders:

-- Index for: ORDER BY CUSTOMER_ID ASC, TRANSACTION_DATE DESC
CREATE INDEX IX_CUST_TXNDATE
    ON TRANSACTION (CUSTOMER_ID ASC, TRANSACTION_DATE DESC);

15.3.3 The INCLUDE Clause (DB2 for LUW)

On DB2 for LUW, you can include additional columns in the leaf pages of a unique index without making them part of the key:

CREATE UNIQUE INDEX IX_CUSTOMER_PK
    ON CUSTOMER (CUSTOMER_ID)
    INCLUDE (FIRST_NAME, LAST_NAME, EMAIL);

The INCLUDE columns are stored in the leaf entries but are not part of the key used for searching or enforcing uniqueness. Their purpose is to enable index-only access — if a query needs only CUSTOMER_ID, FIRST_NAME, LAST_NAME, and EMAIL, DB2 can satisfy it entirely from the index without touching the table.

On z/OS, the equivalent is achieved through different mechanisms, as we discuss in Section 15.7.

15.3.4 Index Compression

For large indexes, compression can significantly reduce storage and improve buffer pool efficiency (more index entries fit in the same memory):

-- LUW: Enable index compression
CREATE INDEX IX_TRANSACTION_ACCT
    ON TRANSACTION (ACCOUNT_ID)
    COMPRESS YES;

On z/OS, index compression is controlled at the index level as well:

-- z/OS: Index compression
CREATE INDEX IX_TRANSACTION_ACCT
    ON TRANSACTION (ACCOUNT_ID)
    COMPRESS YES;

DB2 uses prefix compression for indexes, which is particularly effective when many key values share common prefixes. For example, an index on a VARCHAR column where many values start with the same characters will compress very well.

15.3.5 Other Index Options

PCTFREE: Percentage of free space to leave on each page during CREATE INDEX or REORG.

CREATE INDEX IX_ACCOUNT_CUST
    ON ACCOUNT (CUSTOMER_ID)
    PCTFREE 20;

A PCTFREE of 20 means 20% of each page is left empty, providing room for future inserts and reducing page splits. The trade-off is a larger index.

BUFFERPOOL (z/OS): Assigns the index to a specific buffer pool.

-- z/OS: Assign index to buffer pool BP2
CREATE INDEX IX_CUSTOMER_PK
    ON CUSTOMER (CUSTOMER_ID)
    BUFFERPOOL BP2;

REVERSE SCANS (LUW): Allows efficient scanning of the index in both forward and reverse directions. This is the default in modern DB2 LUW versions.

CREATE INDEX IX_TRANSACTION_DATE
    ON TRANSACTION (TRANSACTION_DATE)
    ALLOW REVERSE SCANS;

15.4 Clustering Indexes

15.4.1 What Clustering Means

A clustering index defines the physical order in which DB2 attempts to store rows in the table. When you insert a new row, DB2 tries to place it on a page near other rows with similar index key values. The result is that rows with adjacent key values are stored on the same or nearby pages.

This is not the same as sorting the table. DB2 does not continuously re-sort the table. It attempts to maintain physical proximity, but over time — as inserts accumulate and pages fill up — the physical order degrades. The degree to which the physical row order matches the index key order is measured by the cluster ratio.

15.4.2 Why Clustering Matters

Clustering has a profound impact on range scan performance. Consider two scenarios for the query WHERE TRANSACTION_DATE BETWEEN '2025-01-01' AND '2025-01-31', where the result is 300,000 rows:

Scenario A: Clustering index on TRANSACTION_DATE. Because the rows are physically ordered by date, all 300,000 qualifying rows are stored on consecutive pages. DB2 reads a contiguous block of (say) 35,000 pages using sequential prefetch. This is a large but efficient read — sequential I/O on modern storage systems is very fast.

Scenario B: No clustering on TRANSACTION_DATE. The 300,000 qualifying rows are scattered randomly throughout the tablespace. Each row might be on a different page. In the worst case, DB2 reads 300,000 different data pages. Even if each page contains multiple qualifying rows, the random I/O pattern is devastating to performance. The query might take 100 times longer than in Scenario A.

The effect is magnified by prefetch. DB2's sequential prefetch reads multiple pages in a single I/O operation — but it can only do this when the pages are physically contiguous. With a high cluster ratio, DB2 triggers sequential prefetch aggressively. With a low cluster ratio, it falls back to random single-page reads.

15.4.3 Defining a Clustering Index

On z/OS:

CREATE INDEX IX_TRANSACTION_DATE
    ON TRANSACTION (TRANSACTION_DATE)
    CLUSTER;

On LUW, clustering is specified with the CLUSTER keyword, and optionally combined with an ALTER TABLE ... ORGANIZE BY or by using multi-dimensional clustering (MDC):

-- LUW: Define a clustering index
CREATE INDEX IX_TRANSACTION_DATE
    ON TRANSACTION (TRANSACTION_DATE)
    CLUSTER;

Critical rule: Each table can have at most one clustering index. This is a logical necessity — you cannot physically order the rows by two different columns simultaneously. Choosing which column(s) to cluster on is one of the most important index design decisions.

15.4.4 Cluster Ratio and Its Degradation

The cluster ratio is a statistic (stored in the catalog) that measures how well the physical row order matches the clustering index order. A cluster ratio of 100% means perfect clustering; a ratio of 50% means half the rows are "out of order" relative to the index.

When a table is first loaded (or after a REORG), the cluster ratio is typically 100% or very close. As inserts and updates occur, the cluster ratio degrades because new rows may not fit on the ideal page and are placed elsewhere.

The rate of degradation depends on: - Insert pattern: If inserts are monotonically increasing in the cluster key (e.g., timestamps), new rows go to the end of the table, and clustering remains high. If inserts are randomly distributed across the cluster key range, clustering degrades quickly. - Free space: PCTFREE and FREEPAGE settings on the tablespace affect how much room is available for new rows near existing ones. - Update pattern: If UPDATE changes the clustering key column, the row might need to move, degrading clustering.

DB2 stores the cluster ratio in the catalog. You can query it to monitor degradation:

-- z/OS: Check cluster ratio
SELECT NAME, CLUSTERING, CLUSTERRATIO
FROM SYSIBM.SYSINDEXES
WHERE TBNAME = 'TRANSACTION'
  AND TBCREATOR = 'MERIDIAN';

-- LUW: Check cluster ratio
SELECT INDNAME, CLUSTERRATIO
FROM SYSCAT.INDEXES
WHERE TABNAME = 'TRANSACTION'
  AND TABSCHEMA = 'MERIDIAN';

When the cluster ratio drops below a threshold — commonly 80% or 90%, depending on the workload — it is time to REORG the tablespace to restore physical order.

15.4.5 Choosing the Clustering Key

The clustering key should be the column (or columns) used most frequently in range scan queries. For a transaction table, common candidates are:

• TRANSACTION_DATE: If most queries filter by date range ("show me all transactions this month").
• ACCOUNT_ID: If most queries retrieve all transactions for a specific account.
• BRANCH_ID: If most queries retrieve all transactions for a specific branch.

You cannot cluster on all of them. You must analyze the workload and choose the access pattern that benefits most from sequential I/O. In Meridian Bank's case, the TRANSACTION table is queried most frequently by ACCOUNT_ID (customers viewing their account history), so clustering on ACCOUNT_ID is the strongest choice — even though date-range reporting queries would benefit from date clustering. The reporting queries typically run as batch jobs where longer execution time is tolerable; the customer-facing queries have strict response time requirements.

This is a design trade-off with no universally correct answer. It requires understanding the workload.

15.5 Index-Only Access — The Holy Grail

15.5.1 What Is Index-Only Access?

Index-only access occurs when DB2 can satisfy a query entirely from an index, without reading any data pages from the table. This is possible when every column referenced in the query — in the SELECT list, WHERE clause, GROUP BY, ORDER BY, and HAVING — exists in the index.

Why is this so powerful? Because it eliminates the most expensive part of an indexed lookup: the random data page read. An index leaf page typically contains many entries (dozens to hundreds), each corresponding to a different data page. If DB2 must fetch the actual row, it performs a random read for each qualifying row. With index-only access, all the needed data is right there in the leaf page.

15.5.2 A Concrete Example

Consider this query that runs thousands of times per day at Meridian Bank:

SELECT CUSTOMER_ID, LAST_NAME, FIRST_NAME
FROM CUSTOMER
WHERE LAST_NAME = 'JOHNSON';

With a plain index on LAST_NAME:

CREATE INDEX IX_CUSTOMER_LASTNAME
    ON CUSTOMER (LAST_NAME);

DB2 uses the index to find all leaf entries where LAST_NAME = 'JOHNSON'. Each leaf entry contains the LAST_NAME value and a RID. DB2 then reads each data page to get CUSTOMER_ID and FIRST_NAME. If there are 500 customers named JOHNSON, that is potentially 500 random data page reads.

Now consider a covering index:

CREATE INDEX IX_CUSTOMER_LASTNAME_COV
    ON CUSTOMER (LAST_NAME, FIRST_NAME, CUSTOMER_ID);

Or, on LUW, using INCLUDE:

CREATE UNIQUE INDEX IX_CUSTOMER_PK
    ON CUSTOMER (CUSTOMER_ID)
    INCLUDE (LAST_NAME, FIRST_NAME);

-- Combined with:
CREATE INDEX IX_CUSTOMER_LASTNAME_COV
    ON CUSTOMER (LAST_NAME)
    INCLUDE (FIRST_NAME, CUSTOMER_ID);

With the covering index, every column in the query (CUSTOMER_ID, LAST_NAME, FIRST_NAME) is in the index. DB2 finds the leaf entries for LAST_NAME = 'JOHNSON' and reads FIRST_NAME and CUSTOMER_ID directly from the leaf entries. Zero data page reads. The 500-customer lookup that previously required 500+ I/O operations now requires perhaps 5-10 leaf page reads.

15.5.3 When the Optimizer Chooses Index-Only Access

The optimizer will consider index-only access whenever all referenced columns exist in an index. But it will only choose index-only access if it estimates the cost to be lower than alternatives. Factors include:

• Number of qualifying rows: For a query that returns a large fraction of the table, a tablespace scan might still be cheaper than an index scan (even index-only), because the tablespace scan benefits from sequential prefetch.
• Index size: A very wide covering index (many INCLUDE columns) might be almost as large as the table itself, reducing the advantage.
• Statistics freshness: If catalog statistics are stale, the optimizer might not realize that index-only access is beneficial.

You can verify that index-only access is being used by examining the access plan:

-- LUW: Explain the query
EXPLAIN PLAN FOR
SELECT CUSTOMER_ID, LAST_NAME, FIRST_NAME
FROM CUSTOMER
WHERE LAST_NAME = 'JOHNSON';

In the EXPLAIN output, look for IXONLY = Y (z/OS) or the absence of a table fetch operator (LUW). On z/OS, the PLAN_TABLE column INDEXONLY will show 'Y' when index-only access is used.

15.5.4 The INCLUDE Clause Strategy

The INCLUDE clause on LUW is specifically designed for index-only access. The strategy is:

1. Make the key columns the ones used in WHERE, ORDER BY, and GROUP BY (these need to be searchable and sorted).
2. Add other columns referenced in the SELECT list as INCLUDE columns (these just need to be present in the leaf page, not searchable).

-- Query pattern:
-- SELECT ACCOUNT_ID, BALANCE, ACCOUNT_TYPE
-- FROM ACCOUNT
-- WHERE CUSTOMER_ID = ?

CREATE INDEX IX_ACCOUNT_CUST_COV
    ON ACCOUNT (CUSTOMER_ID)
    INCLUDE (ACCOUNT_ID, BALANCE, ACCOUNT_TYPE);

INCLUDE columns have lower overhead than key columns because they do not participate in the sort order or uniqueness checking of the index. However, they do increase the size of each leaf entry, which means fewer entries per leaf page, which means a larger index.

15.5.5 Trade-Offs of Covering Indexes

Covering indexes are not free:

• Wider leaf entries: Each INCLUDE column adds to the leaf entry size. This means fewer entries per leaf page, more leaf pages in the index, and more space consumed.
• Write amplification: Every INSERT and every UPDATE to an included column must update the index.
• Diminishing returns: If you add so many columns that the index is nearly as wide as the table, you lose most of the advantage. You have essentially duplicated the table in index form.

The guideline is: cover the most critical and most frequent queries. Do not attempt to cover every possible query — that path leads to over-indexing.

15.6 Composite Indexes and Column Order

15.6.1 Multi-Column Indexes

A composite index (also called a compound index or multi-column index) is an index on two or more columns:

CREATE INDEX IX_TRANSACTION_ACCT_DATE
    ON TRANSACTION (ACCOUNT_ID, TRANSACTION_DATE);

The key values are ordered first by ACCOUNT_ID, then within each ACCOUNT_ID by TRANSACTION_DATE. Think of it like a phone book: sorted first by last name, then by first name within each last name.

15.6.2 The Leading Column Principle (Left Prefix Rule)

This is one of the most important rules in index design. A composite index can be used efficiently only when the query provides a predicate on the leading (leftmost) column(s) of the index. This is called the left prefix rule.

Given the index (ACCOUNT_ID, TRANSACTION_DATE):

The left prefix rule means that column order in a composite index is critical. An index on (A, B, C) supports queries on A, (A, B), and (A, B, C) efficiently — but not queries on B alone, C alone, or (B, C).

15.6.3 Column Order Heuristics

When designing a composite index, use these guidelines for column ordering:

1. Equality columns first: Columns used with = predicates should come before columns used with range predicates (BETWEEN, <, >, LIKE). This maximizes the number of "matching columns" in the index scan.

2. High selectivity columns first (when multiple equality predicates are present): If you have WHERE COUNTRY = 'US' AND CUSTOMER_ID = 12345, CUSTOMER_ID is far more selective (fewer matching rows) and should come first — although in practice, if both are equality predicates, the order matters less for filtering and more for other queries that might use a subset of the index.

3. Consider partial key usage: If you have queries that use only the first column, the composite index serves double duty. An index on (CUSTOMER_ID, ACCOUNT_TYPE) supports both WHERE CUSTOMER_ID = ? and WHERE CUSTOMER_ID = ? AND ACCOUNT_TYPE = ?. But if you also need WHERE ACCOUNT_TYPE = ? alone, you might need a separate index on (ACCOUNT_TYPE).

4. ORDER BY alignment: If a query both filters and sorts, design the index so that the WHERE clause columns come first (for matching), and the ORDER BY columns come next (to avoid a sort operation).

-- Query: SELECT * FROM TRANSACTION
--        WHERE ACCOUNT_ID = ?
--        ORDER BY TRANSACTION_DATE DESC

-- Optimal index:
CREATE INDEX IX_TXN_ACCT_DATE
    ON TRANSACTION (ACCOUNT_ID, TRANSACTION_DATE DESC);

With this index, DB2 can both filter on ACCOUNT_ID (matching scan) and deliver results in the requested order (no additional sort needed).

15.6.4 How Many Columns Is Too Many?

There is no hard limit on the number of columns in a composite index (DB2 for z/OS supports up to 64 key columns; LUW supports up to 64 as well, with a total key length limit of roughly half the page size). But practical limits exist:

• Each additional column widens the key, reducing entries per page and increasing the index size.
• Each additional column increases write overhead (more columns that, if updated, require index maintenance).
• Beyond 3-4 columns, the law of diminishing returns applies. The optimizer may not be able to use all the columns as matching columns.

Most production indexes have 1-4 key columns. Indexes with 5 or more key columns should be justified by specific, well-documented query patterns.

15.7 [z/OS] Partitioned Indexes

On z/OS, tables in partitioned tablespaces have a special relationship with indexes. Understanding partitioned indexes is essential for z/OS DBAs.

15.7.1 Partitioning Index (PI)

When a table is defined in a partitioned tablespace (using range-partitioned universal tablespace, which is the standard in modern DB2 for z/OS), the partitioning index defines how data is distributed across partitions. The leading columns of the partitioning index correspond to the partition key columns defined with PARTITION BY.

The partitioning index is automatically partitioned — each partition of the table has its own index partition. This means: - Index maintenance on one partition does not affect others. - REORG can operate on one partition at a time. - Partition-level operations (like adding a new partition) are independent.

15.7.2 Data-Partitioned Secondary Indexes (DPSI)

A DPSI is a secondary index (an index that is not the partitioning index) that is partitioned in the same way as the data. Each data partition has its own corresponding DPSI partition.

-- z/OS: A secondary index defined as DPSI
CREATE INDEX IX_TXN_STATUS
    ON TRANSACTION (STATUS)
    PARTITIONED;

Advantages of DPSI: - Partition independence: REORG, REBUILD INDEX, and other utilities can operate on one partition without affecting others. - Availability: While one partition is being reorganized, the others remain available for queries.

Disadvantages of DPSI: - Non-partitioned queries are expensive: If a query does not include a predicate on the partition key, DB2 must probe every DPSI partition to find qualifying rows. A query like WHERE STATUS = 'ACTIVE' must search all partitions because STATUS does not correspond to the partitioning key.

15.7.3 Non-Partitioned Secondary Indexes (NPSI)

An NPSI is a secondary index that spans the entire table — it is not partitioned.

-- z/OS: A secondary index defined as NPSI
CREATE INDEX IX_TXN_STATUS
    ON TRANSACTION (STATUS)
    NOT PARTITIONED;

Advantages of NPSI: - Efficient cross-partition lookups: A query on WHERE STATUS = 'ACTIVE' uses a single index search, regardless of how the data is partitioned.

Disadvantages of NPSI: - Reduces partition independence: A REORG of one data partition requires updating the NPSI (since rows may have moved to different RIDs). This creates inter-partition dependencies. - Utility overhead: Operations that affect row placement in one partition may require index rebuild of the NPSI.

15.7.4 Choosing Between DPSI and NPSI

The choice depends on the workload:

• If most queries that use this index also include the partition key in their WHERE clause, use DPSI. The optimizer will probe only the relevant partition(s).
• If queries frequently use this index without the partition key, use NPSI. The cost of scanning all DPSI partitions outweighs the utility overhead of maintaining an NPSI.
• If partition independence and availability are critical (e.g., 24/7 OLTP with rolling partition maintenance), favor DPSI wherever possible and accept the trade-off of requiring partition key predicates.

15.8 [LUW] Index Types

DB2 for LUW offers several index types beyond the standard B+ tree.

15.8.1 Regular (B+ Tree) Indexes

The standard index type, as described throughout this chapter. Suitable for the vast majority of workloads.

15.8.2 Dimension Block Indexes (MDC)

Multi-Dimensional Clustering (MDC) is a LUW-specific feature that allows clustering on multiple dimensions simultaneously. Unlike traditional clustering (which is limited to one index), MDC organizes data into "blocks" based on the values of two or more columns.

CREATE TABLE TRANSACTION (
    TRANSACTION_ID   BIGINT NOT NULL,
    ACCOUNT_ID       INTEGER NOT NULL,
    TRANSACTION_DATE DATE NOT NULL,
    AMOUNT           DECIMAL(15,2),
    STATUS           CHAR(1)
)
ORGANIZE BY DIMENSIONS (TRANSACTION_DATE, STATUS);

DB2 automatically creates dimension block indexes for each specified dimension. These indexes point to blocks (groups of consecutive pages) rather than individual rows.

MDC is particularly powerful for data warehouse workloads where queries filter on multiple dimensions (e.g., date and region, or date and product category). The trade-off is that MDC tables may use more space (blocks must be pre-allocated for each unique combination of dimension values) and the table design is more constrained.

15.8.3 Spatial Indexes

For geospatial data (stored using DB2's Spatial Extender), DB2 for LUW supports spatial indexes based on grid structures:

CREATE INDEX IX_BRANCH_LOCATION
    ON BRANCH (LOCATION)
    EXTEND USING db2gse.spatial_index (1.0, 10.0, 100.0);

These are specialized structures used for geographic proximity queries and are outside the scope of standard index design.

15.8.4 XML Path Indexes

For tables with XML columns, DB2 for LUW supports XML path indexes that index specific elements or attributes within XML documents:

CREATE INDEX IX_CUSTOMER_XML_EMAIL
    ON CUSTOMER (CUSTOMER_DATA)
    GENERATE KEY USING XMLPATTERN
    '/customer/contact/email' AS SQL VARCHAR(254);

These enable efficient XQuery and SQL/XML queries against XML data without parsing entire documents.

15.8.5 Expression-Based Indexes

DB2 for LUW supports creating indexes on expressions, not just column values. We cover these in detail in Section 15.11.

15.9 Index Design Methodology

Designing indexes is not guesswork. It is a systematic process driven by workload analysis.

15.9.1 Step 1 — Analyze the Workload

Before creating a single index, you must understand the workload:

• What are the most frequent queries? Use monitoring tools, application code review, or DB2's activity monitor to identify the top queries by frequency and by total resource consumption.
• What are the most expensive queries? A query that runs once a day but takes 30 minutes is as important as a query that runs 10,000 times per day but takes 10 ms each.
• What is the read/write ratio? A table with 95% reads and 5% writes can support more indexes than a table with 50/50 reads and writes.

15.9.2 Step 2 — Identify Access Patterns

For each critical query, determine:

1. Which columns appear in WHERE clauses? These are candidates for index keys.
2. What predicate types are used? Equality (=), range (BETWEEN, <, >), IN, LIKE? This affects column ordering in composite indexes.
3. Which columns appear in ORDER BY or GROUP BY? An index that delivers rows in the requested order eliminates an expensive sort operation.
4. Which columns appear in SELECT? If a small number of additional columns would make the query index-only, include them.
5. Which columns appear in JOIN conditions? Foreign key columns are common index candidates.

15.9.3 Step 3 — Design Candidate Indexes

For each access pattern, design a candidate index:

• Start with the WHERE clause columns as the key.
• Apply the column ordering heuristics from Section 15.6.3: equality columns first, then range columns.
• Consider adding ORDER BY columns after the WHERE columns.
• Evaluate whether INCLUDE columns would enable index-only access for the most critical queries.
• Check whether an existing index already covers the pattern (perhaps with different column order or a subset of columns).

15.9.4 Step 4 — Consolidate and Eliminate Redundancy

Multiple queries may be served by the same index. Look for opportunities to consolidate:

• An index on (A, B, C) supports queries on A, (A, B), and (A, B, C). You do not need separate indexes on A and (A, B).
• If two queries need indexes on (A, B) and (A, C), you cannot combine them into one index (column order matters). But you can create (A, B, C) if both queries always provide A and the third column.

Eliminate truly redundant indexes. An index on (A) is redundant if you also have an index on (A, B) — the composite index serves all queries that the single-column index would serve.

15.9.5 Step 5 — Estimate Overhead

For each candidate index, estimate:

• Storage: Approximate the index size based on key width, number of rows, and page size.
• Write overhead: How many additional page writes per INSERT, UPDATE, DELETE? Multiply by the transaction rate.
• REORG frequency: Will this index need frequent reorganization?

15.9.6 Step 6 — Test and Measure

Create the indexes in a test environment. Run the critical queries and measure:

• Access plan changes: Use EXPLAIN to verify the optimizer is using the index as expected.
• Elapsed time improvement: Measure actual query response times before and after.
• Write overhead: Measure INSERT/UPDATE/DELETE performance with the new indexes.
• Buffer pool impact: Are the new indexes consuming buffer pool space and causing other objects to be paged out?

Only deploy indexes that demonstrate measurable benefit. Remove any that do not.

15.10 Anti-Patterns in Indexing

15.10.1 Over-Indexing

The most common anti-pattern. Symptoms: - A table has more indexes than columns. - Every query is fast, but INSERT/UPDATE/DELETE operations are slow. - REORG takes hours because every index must be rebuilt. - The combined size of all indexes exceeds the table itself.

Over-indexing often happens when developers create an index for every slow query without checking whether an existing index could serve the purpose. It also happens when indexes are never reviewed or dropped — the table accumulates indexes over years, some of which support queries that no longer exist.

Remedy: Periodic index review. Query the catalog for indexes with low usage. Drop indexes that the optimizer never (or rarely) selects. We cover monitoring in Section 15.12.

15.10.2 Function-on-Column Prevents Index Use

DB2 cannot use a standard index when the indexed column is wrapped in a function:

-- This index exists:
CREATE INDEX IX_CUSTOMER_LASTNAME ON CUSTOMER (LAST_NAME);

-- This query CANNOT use the index efficiently:
SELECT * FROM CUSTOMER
WHERE UPPER(LAST_NAME) = 'JOHNSON';

The function UPPER() transforms the column value before comparison. DB2 would need to apply UPPER() to every index entry to check for a match, which defeats the purpose. The optimizer will likely choose a tablespace scan.

Remedies: - On LUW, create an expression-based index: CREATE INDEX IX_CUST_UPPER_LAST ON CUSTOMER (UPPER(LAST_NAME)); - On z/OS, create a generated column and index it (see Section 15.11). - Rewrite the query to avoid the function: if data is always stored in uppercase, remove the UPPER() call. If mixed case is possible, standardize on storage format.

15.10.3 Implicit Data Type Conversions

When the data type of a predicate value does not match the column type, DB2 may apply an implicit conversion that prevents index use:

-- ACCOUNT_NUMBER is CHAR(10)
-- This query passes a numeric literal:
SELECT * FROM ACCOUNT WHERE ACCOUNT_NUMBER = 12345;

DB2 must convert either the column or the literal to make them comparable. If it converts the column (applying a function to it), index use is lost. Always match data types in predicates.

15.10.4 Redundant Indexes

An index on (CUSTOMER_ID) is redundant if an index on (CUSTOMER_ID, ACCOUNT_TYPE) also exists. The composite index handles everything the single-column index does. The redundant index wastes storage and adds write overhead.

Exception: In some rare cases, a narrower index is preferred by the optimizer because it has fewer leaf pages and thus a faster non-matching scan. But this is uncommon enough that the default recommendation is to drop the redundant index.

15.10.5 Indexing Low-Cardinality Columns Alone

An index on a column with very few distinct values (e.g., STATUS with values 'A', 'I', 'C') is rarely useful by itself. If 40% of rows have STATUS = 'A', using the index to find them requires reading 40% of the table via random I/O — worse than a tablespace scan with sequential prefetch.

Low-cardinality columns can be valuable as trailing columns in a composite index (for additional filtering after a selective leading column), but as standalone indexes, they are usually wasteful.

15.10.6 Leading Wildcards

-- Index on LAST_NAME exists
SELECT * FROM CUSTOMER WHERE LAST_NAME LIKE '%SON';

A LIKE predicate with a leading wildcard (%SON) cannot use the index for matching. DB2 must scan all entries because the search value does not specify a prefix. Only trailing wildcards (JOHN%) allow index matching.

15.11 Expression-Based Indexes and Partial Indexes

15.11.1 Expression-Based Indexes (LUW)

DB2 for LUW allows you to create an index on an expression rather than a raw column value:

-- Index on uppercase last name
CREATE INDEX IX_CUST_UPPER_LAST
    ON CUSTOMER (UPPER(LAST_NAME));

-- Index on year extracted from date
CREATE INDEX IX_TXN_YEAR
    ON TRANSACTION (YEAR(TRANSACTION_DATE));

-- Index on computed value
CREATE INDEX IX_ACCOUNT_BALANCE_TYPE
    ON ACCOUNT (BALANCE * CASE WHEN ACCOUNT_TYPE = 'C' THEN 1 ELSE -1 END);

These indexes are maintained automatically: whenever the underlying columns change, DB2 recomputes the expression and updates the index.

Expression-based indexes solve the function-on-column problem described in Section 15.10.2. If your application always queries WHERE UPPER(LAST_NAME) = ?, the expression-based index makes that query indexable.

15.11.2 Generated Columns and Indexes (z/OS)

DB2 for z/OS does not support expression-based indexes directly. The equivalent technique is to define a generated column (also called a computed column) and create a standard index on it:

-- z/OS: Add a generated column
ALTER TABLE CUSTOMER
    ADD COLUMN LAST_NAME_UPPER VARCHAR(100)
    GENERATED ALWAYS AS (UPPER(LAST_NAME));

-- Index the generated column
CREATE INDEX IX_CUST_UPPER_LAST
    ON CUSTOMER (LAST_NAME_UPPER);

DB2 for z/OS automatically maintains the generated column — whenever LAST_NAME is inserted or updated, LAST_NAME_UPPER is recomputed. The optimizer recognizes that a predicate like WHERE UPPER(LAST_NAME) = 'JOHNSON' can be satisfied using the generated column and its index.

15.11.3 Partial Index Equivalent Patterns

Some databases support partial indexes (also called filtered indexes) — indexes that include only rows matching a condition. Standard DB2 does not have a native partial index syntax, but you can approximate the behavior:

On LUW, using expression-based indexes with CASE:

-- Index only rows where STATUS = 'ACTIVE'
-- (Entries where STATUS != 'ACTIVE' will have NULL key, and NULLs
-- are stored but can be skipped in queries)
CREATE INDEX IX_ACTIVE_ACCOUNTS
    ON ACCOUNT (CASE WHEN STATUS = 'A' THEN ACCOUNT_ID ELSE NULL END);

On z/OS, using generated columns:

ALTER TABLE ACCOUNT
    ADD COLUMN ACTIVE_ACCOUNT_ID INTEGER
    GENERATED ALWAYS AS (CASE WHEN STATUS = 'A' THEN ACCOUNT_ID ELSE NULL END);

CREATE INDEX IX_ACTIVE_ACCT
    ON ACCOUNT (ACTIVE_ACCOUNT_ID);

These patterns create indexes where non-qualifying rows have NULL keys. The index entries for NULLs exist but are clustered together, and queries that filter on the non-NULL expression effectively use a smaller, more efficient index.

These are workarounds, not true partial indexes. They add storage for the NULL entries and require careful query construction to match the expression.

15.12 Monitoring Index Usage

An index that nobody uses is pure overhead. It consumes storage, slows writes, and complicates REORG operations. Identifying and removing unused indexes is one of the highest-value activities a DBA can perform.

15.12.1 [LUW] Index Usage Monitoring

On DB2 for LUW, the monitoring infrastructure tracks index usage:

-- Check when indexes were last used
SELECT TABSCHEMA, TABNAME, INDNAME,
       LASTUSED
FROM SYSCAT.INDEXES
WHERE TABSCHEMA = 'MERIDIAN'
ORDER BY LASTUSED NULLS FIRST;

The LASTUSED column in SYSCAT.INDEXES shows the date the index was last used in an access plan. Indexes with NULL or very old LASTUSED values are candidates for removal.

For more detailed usage statistics, use the monitoring table functions:

-- Index read/write activity
SELECT SUBSTR(TABNAME, 1, 30) AS TABLE_NAME,
       SUBSTR(IID_TO_NAME(TABSCHEMA, TABNAME, IID), 1, 30) AS INDEX_NAME,
       INDEX_SCANS,
       INDEX_ONLY_SCANS,
       INDEX_JUMP_SCANS,
       ROWS_READ,
       ROWS_INSERTED,
       ROWS_DELETED
FROM TABLE(MON_GET_INDEX('MERIDIAN', NULL, -2)) AS T
ORDER BY INDEX_SCANS;

15.12.2 [z/OS] Index Usage Monitoring

On z/OS, index usage can be monitored through:

1. Real-Time Statistics (RTS): DB2 for z/OS maintains real-time statistics that include the number of index scans:

SELECT DBNAME, TSNAME, IXNAME,
       NLEVELS, TOTALENTRIES,
       REORGHIGHWATERMARK
FROM SYSIBM.SYSINDEXSPACESTATS
WHERE DBNAME = 'MERIDIAN';

2. IFCID tracing: Performance trace records (IFCID 0003, etc.) capture which indexes are used in access plans. However, tracing adds overhead and is typically used for targeted analysis rather than continuous monitoring.

3. DB2 Catalog statistics: After running RUNSTATS, the catalog tables contain updated statistics that include cardinality, cluster ratio, and other metrics.

SELECT NAME, CREATOR, TBNAME, FIRSTKEYCARD,
       FULLKEYCARD, CLUSTERRATIO, NLEAF, NLEVELS
FROM SYSIBM.SYSINDEXES
WHERE TBCREATOR = 'MERIDIAN'
ORDER BY TBNAME, NAME;

15.12.3 When to Drop an Index

Consider dropping an index when:

1. It has not been used in months. An index that the optimizer never selects is not providing value. But verify first — it might be used by a quarterly or annual batch job.

2. It is redundant. An index on (A) is redundant if (A, B) exists. But check whether any queries rely on the narrower index for index-only access or for a different scan direction.

3. Its maintenance cost exceeds its benefit. If an index is used by 10 queries per day but slows down 100,000 inserts per day, the math does not work.

4. The application has changed. Queries that once used the index may have been rewritten or removed.

Before dropping an index in production, always: - Verify the index is not used by any critical query (check recent EXPLAIN output and application SQL). - Save the CREATE INDEX statement so you can recreate it if needed. - Drop during a maintenance window. - Monitor performance after the drop for any regressions.

15.13 The Meridian Bank Index Strategy

Now we apply everything we have learned to Meridian National Bank's database. We will design indexes for the four core tables: CUSTOMER, ACCOUNT, TRANSACTION, and LOAN. For each index, we state the specific queries it supports and the reasoning behind the design.

15.13.1 CUSTOMER Table Indexes

Table profile: 10 million rows. Moderate insert rate (new customer onboarding). High read rate (customer lookups in every transaction, online banking, call center).

-- 1. Primary key index (automatically created with PK constraint)
--    Supports: Direct customer lookup by ID
--    Used by: Every transaction, every account query, every join
CREATE UNIQUE INDEX IX_CUSTOMER_PK
    ON CUSTOMER (CUSTOMER_ID);

-- 2. Customer lookup by SSN (unique, used for identity verification)
--    Supports: Call center identity verification, fraud detection
--    Unique because SSN should be unique per customer
CREATE UNIQUE INDEX IX_CUSTOMER_SSN
    ON CUSTOMER (SSN);

-- 3. Customer lookup by name (non-unique, common search pattern)
--    Supports: Call center search ("customer says their name is...")
--    INCLUDE columns enable index-only access for the search results
CREATE INDEX IX_CUSTOMER_NAME
    ON CUSTOMER (LAST_NAME, FIRST_NAME)
    INCLUDE (CUSTOMER_ID, PHONE_NUMBER);

-- 4. Customer lookup by email (unique for online banking)
--    Supports: Online banking login, password reset
CREATE UNIQUE INDEX IX_CUSTOMER_EMAIL
    ON CUSTOMER (EMAIL);

Rationale: Four indexes is reasonable for a heavily-read table. The name index with INCLUDE columns supports the most common call center workflow (search by name, display customer ID and phone) without table access. SSN and email indexes enforce business uniqueness rules while supporting lookup patterns.

Clustering choice: CUSTOMER_ID (the primary key) is the clustering index. Since most customer lookups are by ID and joins from other tables use CUSTOMER_ID, keeping rows in ID order optimizes the most frequent access pattern.

15.13.2 ACCOUNT Table Indexes

Table profile: 25 million rows (customers may have multiple accounts). Moderate insert rate. Very high read rate (every transaction references an account, online banking shows all accounts for a customer).

-- 1. Primary key index
--    Supports: Direct account lookup, join target from TRANSACTION
CREATE UNIQUE INDEX IX_ACCOUNT_PK
    ON ACCOUNT (ACCOUNT_ID);

-- 2. All accounts for a customer (critical for online banking)
--    Supports: "Show all my accounts" query
--    INCLUDE columns enable index-only access for account summary display
CREATE INDEX IX_ACCOUNT_CUSTOMER
    ON ACCOUNT (CUSTOMER_ID)
    INCLUDE (ACCOUNT_TYPE, BALANCE, STATUS);

-- 3. Account number lookup (for external-facing account numbers)
--    Supports: Payment processing, wire transfers, customer inquiries
CREATE UNIQUE INDEX IX_ACCOUNT_NUMBER
    ON ACCOUNT (ACCOUNT_NUMBER);

Rationale: Three indexes. The CUSTOMER_ID index is the workhorse — the "show all my accounts" page is one of the highest-traffic operations in online banking, and the INCLUDE columns make it index-only. The ACCOUNT_NUMBER index supports external-facing operations where customers or other banks reference the account by its external number.

Clustering choice: CUSTOMER_ID. Customers tend to access all their accounts together, and new accounts for existing customers should be stored near the customer's existing accounts.

15.13.3 TRANSACTION Table Indexes

Table profile: 500 million rows and growing rapidly (this is the largest and busiest table). Very high insert rate (thousands per second during peak). High read rate for recent transactions; lower read rate for historical.

-- 1. Primary key index
--    Supports: Direct transaction lookup
CREATE UNIQUE INDEX IX_TRANSACTION_PK
    ON TRANSACTION (TRANSACTION_ID);

-- 2. Transactions by account (the primary access pattern)
--    Supports: "Show recent transactions for this account"
--    Descending date: most recent transactions appear first in leaf pages
--    CLUSTERING: This is the clustering index
CREATE INDEX IX_TRANSACTION_ACCT_DATE
    ON TRANSACTION (ACCOUNT_ID, TRANSACTION_DATE DESC)
    CLUSTER;

-- 3. Transactions by date range (for reporting and audit)
--    Supports: Daily/monthly transaction reports, regulatory audit queries
--    This is NOT the clustering index — date-range reports accept longer runtime
CREATE INDEX IX_TRANSACTION_DATE
    ON TRANSACTION (TRANSACTION_DATE);

-- 4. Transaction reference number (for dispute investigation)
--    Supports: Looking up a specific transaction by its external reference
CREATE UNIQUE INDEX IX_TRANSACTION_REF
    ON TRANSACTION (REFERENCE_NUMBER);

Rationale: Four indexes on a 500-million-row, write-heavy table. Every index must justify its existence. The ACCOUNT_ID + DATE composite is the clustering index because the dominant query pattern is "show me the recent transactions for account X." Clustering means those rows are physically adjacent, enabling fast sequential reads. The DATE index supports reporting queries — these run less frequently and can tolerate the non-clustered access pattern.

We deliberately did not add a covering index with INCLUDE columns for the transaction detail query. The TRANSACTION table is written so heavily that the additional width of INCLUDE columns on the clustered index would slow inserts. The clustering itself provides sufficient read performance for the OLTP workload.

Clustering choice: (ACCOUNT_ID, TRANSACTION_DATE DESC). Transactions for the same account, in reverse chronological order, are stored together. When a customer views their recent transactions, DB2 reads a small contiguous block of pages.

15.13.4 LOAN Table Indexes

Table profile: 2 million rows. Low insert rate (new loans are infrequent). Moderate read rate (loan status inquiries, payment processing). Periodic batch processing (monthly payment runs, delinquency reports).

-- 1. Primary key index
--    Supports: Direct loan lookup
CREATE UNIQUE INDEX IX_LOAN_PK
    ON LOAN (LOAN_ID);

-- 2. Loans by customer (customer wants to see all their loans)
--    Supports: Online banking loan display, call center inquiries
--    INCLUDE columns for index-only access on loan summary
CREATE INDEX IX_LOAN_CUSTOMER
    ON LOAN (CUSTOMER_ID)
    INCLUDE (LOAN_TYPE, ORIGINAL_AMOUNT, CURRENT_BALANCE, STATUS);

-- 3. Loans by status (for batch processing)
--    Supports: "Find all delinquent loans", "Find all loans pending approval"
--    Combined with loan type for the monthly payment batch
CREATE INDEX IX_LOAN_STATUS_TYPE
    ON LOAN (STATUS, LOAN_TYPE)
    INCLUDE (CUSTOMER_ID, CURRENT_BALANCE, NEXT_PAYMENT_DATE);

-- 4. Loans by next payment date (for payment reminder batch)
--    Supports: "Find all loans with payment due in next 7 days"
CREATE INDEX IX_LOAN_NEXT_PAYMENT
    ON LOAN (NEXT_PAYMENT_DATE)
    INCLUDE (LOAN_ID, CUSTOMER_ID, CURRENT_BALANCE);

Rationale: Four indexes on a smaller, read-heavy table. The LOAN table has a lower write rate, so we can afford more generous covering indexes. The STATUS + TYPE index with INCLUDE columns supports the monthly batch processing that identifies delinquent loans, calculates payments, and generates notices — all index-only. The NEXT_PAYMENT_DATE index supports the payment reminder batch.

Clustering choice: CUSTOMER_ID. Loans for the same customer should be physically adjacent because the most frequent online access pattern is "show all loans for this customer."

15.13.5 Summary of Index Count

Total: 15 indexes across 4 tables. Each one has a stated purpose and documented query pattern. None is redundant. This is a disciplined, workload-driven index strategy.

15.14 Spaced Review: Retrieving Prior Knowledge

Before we close this chapter, let us revisit key concepts from earlier chapters. This spaced retrieval strengthens long-term retention.

From Chapter 3 (DB2 Architecture)

Question: When DB2 reads an index page, where does it look first — on disk or in memory? Why?

Answer: DB2 first checks the buffer pool. The buffer pool is an in-memory cache of recently accessed pages. Index root and upper-level non-leaf pages are accessed so frequently that they almost always reside in the buffer pool, making the physical I/O cost of an index traversal much lower than the theoretical maximum. This is why a B+ tree of height 4 rarely incurs 4 physical reads — the top 2-3 levels are cached.

From Chapter 11 (Normalization)

Question: How does normalization affect index design? Consider a denormalized table with customer name, address, and transaction details in one table versus a normalized design with separate CUSTOMER and TRANSACTION tables.

Answer: In the normalized design, you need indexes to support joins between CUSTOMER and TRANSACTION (typically on CUSTOMER_ID in both tables). The denormalized design avoids the join but creates a wider table, which means fewer rows per page, larger tablespace scans, and potentially more indexes to support different access patterns on the wider table. Normalization creates more tables but each table is narrower and more focused, allowing more precise indexes that are more likely to achieve index-only access.

From Chapter 14 (Physical Design and Tablespaces)

Question: What is the relationship between page size, rows per page, and index effectiveness?

Answer: Page size affects how many rows fit on a data page and how many index entries fit on an index page. Larger page sizes (8 KB, 16 KB, 32 KB) mean more entries per index page, which keeps the B+ tree shorter and reduces I/O. But larger pages also mean that reading a single page brings in more data — wasteful if you only need one row from the page. For OLTP workloads with point lookups, the default 4 KB page size is often optimal. For DSS workloads with range scans, larger page sizes can improve sequential read throughput.

Summary

Index design is where theoretical understanding meets practical engineering judgment. The B+ tree provides the mechanical foundation — a structure that guarantees logarithmic lookup cost regardless of table size. But the decisions about which indexes to create, what columns to include, and what column order to use are driven by workload analysis, not by theory alone.

The key principles to carry forward:

1. B+ trees map directly to I/O. Tree height determines lookup cost. A height-3 tree means 3 index page reads plus 1 data page read. Caching the upper levels reduces this further.

2. Clustering is the most impactful single decision. It determines whether range scans read contiguous pages (fast) or random pages (slow). You get one clustering index per table. Choose wisely based on the dominant access pattern.

3. Index-only access eliminates the most expensive step. When all needed columns are in the index, DB2 never reads the table. This transforms the I/O profile of a query. Use INCLUDE columns judiciously to enable it.

4. Column order in composite indexes follows the left prefix rule. The leading column must be present in the query for the index to be used efficiently. Put equality predicates first, then range predicates, then ORDER BY columns.

5. Every index is a cost-benefit trade-off. Reads get faster, writes get slower. More indexes mean more storage, more maintenance, more REORG time. The right number of indexes depends on the workload, not on a fixed rule.

6. Monitor and maintain. Unused indexes are pure overhead. Cluster ratios degrade over time. Statistics must be kept current. Index design is not a one-time activity — it is an ongoing operational discipline.

In the next chapter, we will explore how the DB2 optimizer uses these indexes — the cost-based optimization process that turns your index design decisions into execution plans.

Return to Part III: Database Design | Continue to Exercises | Quiz