DB2 - Indexes

Db2 - Indexes for Performance

This chapter explores Db2 indexes, a critical tool for accelerating query performance. We’ll cover their types, creation, and design best practices.

What is an Index?

An index is a data structure that provides a fast lookup path to data in a table, much like the index in the back of a book allows you to quickly find a topic without reading every page. Without an index, Db2 would have to perform a ‘full table scan’, reading every row to find the data that matches your query’s conditions, which is very slow for large tables.

Types of Indexes

Unique vs. Non-Unique Indexes

A Unique Index enforces that no two rows in the table can have the same value in the indexed column(s). It is automatically created when you define a PRIMARY KEY or UNIQUE constraint. A Non-Unique Index allows duplicate values and is used purely for performance.

Clustering Indexes

A Clustering Index determines the physical order of data in a table. Because data is physically sorted, it is highly efficient for range queries (e.g., WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'). A table can have only one clustering index.

INCLUDE Indexes

An index with an INCLUDE clause contains extra column data directly in its leaf nodes. This allows for ‘index-only access’, where Db2 can answer a query entirely from the index without ever touching the table data, providing a significant performance boost.

Other Advanced Indexes

• Indexes on Expressions: Create an index on the result of a function or expression, useful when queries frequently filter on calculated values (e.g., UPPER(last_name)).
• Random-key Indexes: Helps to mitigate page contention (‘hot spots’) in environments with very high-volume, concurrent inserts.
• Block Indexes: Used automatically with Multidimensional Clustering (MDC) tables to provide efficient, multi-dimensional data access.

Index Management

Creating an Index

Use the CREATE INDEX statement. The syntax is straightforward, but choosing the right columns and options is key.

-- Syntax
CREATE [UNIQUE] INDEX <index_name>
ON <table_name> ( <column_name> [ASC|DESC], ... )
[INCLUDE ( <include_column>, ... )]
[CLUSTER];

-- Example 1: A unique index on the email column
CREATE UNIQUE INDEX UQ_CUST_EMAIL
ON CUSTOMERS (EMAIL);

-- Example 2: A composite, clustering index for an Orders table
-- This is great for finding all items within a specific order
CREATE INDEX IDX_ORDER_ITEMS
ON ORDER_ITEMS (ORDER_ID ASC, ITEM_ID ASC)
CLUSTER;

-- Example 3: An index with an INCLUDE clause for index-only access
-- This query can be answered using only the index:
-- SELECT last_name, first_name FROM employees WHERE department_id = 100;
CREATE INDEX IDX_EMP_DEPT
ON EMPLOYEES (DEPARTMENT_ID)
INCLUDE (LAST_NAME, FIRST_NAME);

Dropping an Index

If an index is no longer needed or is harming write performance, you can remove it with DROP INDEX.

-- Syntax
DROP INDEX <index_name>;

-- Example
DROP INDEX IDX_EMP_DEPT;

Index Design Best Practices

• Index Selectively: Index columns used frequently in WHERE, JOIN, ORDER BY, and GROUP BY clauses.
• Use Composite Indexes: If you frequently filter on multiple columns, create a single composite index (e.g., on (last_name, first_name)) rather than two separate indexes. The column order matters!
• Don’t Over-Index: Indexes are not free. They consume disk space and slow down write operations (INSERT, UPDATE, DELETE) because the index must also be updated. Find the right balance.
• Monitor Index Usage: Use Db2’s monitoring tools to find unused indexes, which are candidates for dropping.
• Keep Statistics Current: The Db2 optimizer relies on statistics about your data to decide whether to use an index. Run the RUNSTATS command regularly, especially after large data changes.

Troubleshooting: Is My Index Being Used?

The db2exfmt tool generates a detailed report of the optimizer’s access plan for a query. This is the definitive way to see if your indexes are being used effectively.

-- 1. Explain the query without executing it
EXPLAIN PLAN FOR SELECT * FROM EMPLOYEES WHERE LAST_NAME = 'Smith';

-- 2. Format the explain data into a readable report
db2exfmt -d <database_name> -g -1 -o smith_query_plan.txt

-- Now, inspect smith_query_plan.txt. Look for an 'IXSCAN' (Index Scan)
-- operation. If you see 'TBSCAN' (Table Scan), the index was not used.