I've been working quite a bit on a query that's not operating very efficiently on a DB2 database. The thing will return up to 30k rows or so and take up to 10 seconds. I've been working on getting this thing to run faster and I think I've finally tracked it down to an improper index. A new and more appropriate index should fix the problem, but I'm not entirely sure how this should be ordered as there appears to be different ways for different data.

The query itself isn't terribly complicated. Here's a brief rundown:

ON A.ID1 = B.A_ID1
--A few inner joins and some left joins--
B.TYPE_CODE IN (:parameter) --No more than four values within the IN clause

A few details about these tables:

  • A has about 900k rows and B has about 2.7 million.
  • B.TYPE_CODE has 15 possible values. Stats are accurate. The four parameters together are contained in about 400k rows (about 15% of all entries in the table). The A_ID1/2 columns are much more unique, occurring 15 times at most, of course.
  • B already has an index on (TYPE_CODE, A_ID1, A_ID2). This is being used, but it's not optimal at all
  • There are 6 rows being selected by the DB2-optimized query. It grabs A_ID1 and 2 along with TYPE_CODE and FIELD, of course, but it's also grabbing B.ID1 and B.ID2, which together form the primary key for B. These fields are not used in the original query, but the optimizer is including them, although I don't think they're used in any joins or filters. They're just there.
  • B will have far more reads than write operations on it

The join between A and B is taking a third of the query cost. The explain plan shows a FETCH operation that uses a particular index four times. I was able to get a VERY detailed explain plan report and found that this operation is returning all 400k rows, then performing the join, which only needs about 26k of them.

I found a short-circuit method that added this to the predicate:


Logically this doesn't affect the result set, but it does tweak the optimizer. Adding this cut the query time in half. Checking the detailed explain showed that it added an additional filter factor that reduced the number of estimated rows in that FETCH operation to 26k or so, which is about right. This in turn reduced the estimate for the number of rows from A that would be returned overall and enabled index scans on A that hadn't been used prior due to the optimizer thinking it could be grabbing nearly half of A. The result was a much faster query. However, the COALESCE is a hacky bit and not suited for production, but it was handy for finding an issue.

So now it falls to creating a new index on B to fix this problem. The DB2 query analyzer suggested an index on all six fields, starting with B.TYPE_CODE and following with the rest in no particular logical order. This would cover all selected fields and the table itself would not need to be touched, which certainly has its advantages. It has also been suggested that an index on (B.A_ID1, B.A_ID2, B.TYPE_CODE) could be used as the more selective columns are first, which would narrow the results faster. I've seen different suggestions based on the cardinality of the data, so I'm wondering if anyone has some advice on how to construct the index here. I've been doing a lot of reading on indexes lately, but it can be difficult to find a good guide on certain aspects.

UPDATE: Well, DB2 refuses to use the new index and keeps using the existing index of (TYPE_CODE, B.ID1, B.ID2), and those latter two are the primary key for B, not to be confused with B.A_ID1, B.A_ID2. I think it has to to do with the optimized query grabbing B.ID1 and B.ID2 even though the original query doesn't touch those fields and using them as a SORTKEY. It may be that any index will need to include those fields.

  • What member of the DB2 family are you using? DB2 for i, LUW, or z/OS?
    – WarrenT
    Apr 10, 2013 at 23:08
  • Which is more selective, ID1 or ID2?
    – WarrenT
    Apr 10, 2013 at 23:26
  • It looks like a slam dunk for an index on B(Id1, Id2) or B(Id1, Id2, Type_Code) to improve performance. At least this index would support the join condition more simply, leaving the type_code check to the end, instead of forcing it at the beginning. The COALESCE may prevent the optimizer using the index and thus speed things up, paradoxically. Apr 11, 2013 at 0:37
  • ID1 and ID2 are both BIGINTs that are randomly generated, so I don't think that either would be more or less selective than the other. There is an index on ID1 and ID2 within both tables. TYPE_CODE is indexed separately. The COALESCE didn't affect the explain structure on the FETCH operation, strangely enough. It used the same index both times. All it seemed to do (from what I saw in the detailed explain plan) was add an additional filter factor that narrowed the results from 400k to 26k, which in turn reduced the estimated results elsewhere in the query and allowed indexes to be used. Apr 11, 2013 at 15:09
  • And we're using DB2 for LUW. Apr 11, 2013 at 15:10

1 Answer 1


If ID1 is more selective than ID2, then construct your index over B with (ID1, ID2, Type_Code) , and make sure there is an index over A by (ID1, ID2).

If ID2 is more selective than ID1, then construct your index over B with (ID2, ID1, Type_Code) , and make sure there is an index over A by (ID2, ID1).

If you are using DB2 for i, then you might also try an Encoded Vector Index [EVI] over B on (Type_Code), in combination with an index by (ID1, ID2) [or the reverse order, as above]. However, the EVI seems unlikely to help in this case.

  • Thanks. We're going to try an index on ID1, ID2, TYPE_CODE. Hopefully this gets the optimizer to accurately estimate the number of results that will come through the join and utilize a better access plan without resorting to the no-op COALESCE. I figure I'll also try the query with and without the parameter in the IN clause and use string replacement to see if the use of distribution statistics outweighs the compile overhead. Apr 11, 2013 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.