Design of Indexing
Sanjay Rajput
Sanjay Rajput, Syntel Inc
2
Indexing and Clustering
One of the major aspects of performance enhancement in DB2 is an Index.
Changes to the indexes of the DB2 tables used in the SQL query processing of a
batch application would have a direct impact on the performance of the query and
hence so to the Batch cycle time. So it becomes mandatory to include Indexing
and clustering of indexes as a vital metric for a performance tuning exercise.
Let’s check out some of the important highlights of the concept of indexing and
clustering.
Points to Mind:
1) The primary advantage of indexes is the ability to process a small
percentage of the rows efficiently with minimal I/O and CPU usage.
2) A-clustering index improves performance for processing a larger
percentage of rows (less than 30 to 50 percent).
3) Whenever DB2 loads or reorganizes a table, it must build or rebuild each
index to it.
4) The costs of changing indexes are often more than the costs of changing the
data.
5) Primary keys and foreign keys are often searched or joined over a small
percentage of rows and are good candidates for indexes. Indeed, the
primary key must have a unique index to guarantee unique values in the
column.
6) If there is no index on the foreign key, an update of a primary key value
requires a table space scan of each dependent table.
7) When a row is deleted from a parent table and no index exists on the
foreign key, it is necessary to do a table space scan on each dependent table
to enforce the delete rule.
8) Joins are often performed on the primary key and foreign key columns;
therefore, an index on these columns makes the join much more efficient in
most cases.
Sanjay Rajput, Syntel Inc
3
9) The keyword CLUSTER specified when the index is created, instructs
DB2 to maintain the rows on the data pages in sequence according to the