Photo by Sara Haj-Hassan
My wife and I set a goal early in our married life. It was to pay for our daughters’ university education (at a school of their choosing) so they could start their adult lives debt free. We were able to keep that promise, in part due to the opportunity to tune DB2.
I have written many articles and presentations on DB2 indexes through the past 20 years or so, as have many other capable authors. In my years of writing, the three main truths become evident when tuning applications: do no more I/O than is necessary to return the answer set, reduce the amount of physical I/O by using memory to efficiently store the most highly used data, and reduce the amount of logical I/O by providing indexes to DB2 that find qualifying rows efficiently.
Most articles discuss many different aspects of index tuning – there isn’t a shortage of concepts and parameters to choose from. In this short article, I’ll concentrate on a few examples of how things go awry.
Normally, customers have attempted some tuning themselves before looking for outside help. They have added some indexes, but they often miss finding the right ones. But after they’ve added another index that doesn’t help (or three more indexes for that matter), they are very reluctant to drop these new indexes when no good result is obtained. The most common answer I hear is: “They might help in the future”. I tell them to remove the indexes now, and add them back later if there is still a problem, but they are still reluctant. Apparently, it takes a consultant to remove indexes in case something goes wrong and a scapegoat is needed. To date, I’ve not been blamed.
I remember one case in an executive query application where about 15 tables had 10 indexes each. After analysis, I recommended between one and three indexes per table. Despite a good relationship with the customer, it took three weeks before the customer would implement the recommendations.
Don’t Add More Indexes with the Same Columns
There are well publicized cases where several indexes using the same columns in different orders have solved a performance issue. I’ve heard those stories too. More frequently, the problem isn’t solved. My current worst case is 11 indexes on a four-column table. That case was an upgrade from 14 indexes on a nine-column table.
An examination of index columns often finds redundancy. Not only do more indexes elongate bind times, but they also consume CPU time and take buffer pool space when they are updated. Particularly in OLTP applications, less is better, however the old adage of no more than three indexes has long gone away.
Match Your Indexes to Your SQL
A safe generalization is that any web or online query (SELECT) should have an index to support the WHERE and the ORDER BY clauses.
Cardinality is another key attribute when discussing indexes. Its definition is, for tables, the number of rows in a table, and for indexes and columns, the number of distinct values within the index or column. An old myth is to place the highest cardinality first within an index. The reality is different –the columns known most often, and specified with equal predicates, should be placed first in the index regardless of relative cardinalities.
One European customer had many indexes with 22 and even 27 columns. The highest MATCHCOLS was three. When I suggested that the additional columns were of limited value, they suggested they might help with index only access (they didn’t really know whether the columns would help, but they knew about index only as an access path). It didn’t take long to identify a single column in position 4 that was never used in predicates. It took some convincing to remove this column from the index.
When altering an index design, some quiet reflection is a good thing. Removing indexes can be as important as adding the right indexes.
About the Author
Martin Hubel is as much a permanent fixture at IDUG events as is humanly possible. This has resulted in recognition in the IDUG Volunteer Hall of Fame. Martin teaches, consults, and speaks worldwide on DB2 issues in a collaborative manner. He can be reached at firstname.lastname@example.org.