I'm working on optimizing some queries for a DB2 database. Most of them are running fine, but a few with rather large returned datasets (up to 30k rows or so) are giving me some trouble. There's nothing terribly complicated about these queries. They select perhaps 15 different columns from up to about a dozen joins, with some inner joins and left joins. There are a few IN clauses that reduce the dataset with parameters and subqueries. It's nothing special.I've been looking at some indexes, but without a great deal of luck. The thing is, in attempting to force the use of a new index that wasn't being utilized, I stumbled upon a nifty trick in DB2: A no-op COALESCE in the predicate that can potentially reduce cost and runtime.Basically, it works like this:\[code\]SELECT ...FROM AJOIN BON A.ID1 = B.ID1AND A.ID2 = B.ID2--More inner and left joins--WHEREB.TYPE_CODE IN 
parameter)AND A.X IN (--subquery--)ANDCOALESCE(B.TYPE_CODE,B.TYPE_CODE) = B.TYPE_CODE\[/code\]The COALESCE does nothing from a logical standpoint and B.TYPE_CODE is never null. However, this tricks the DB2 optimizer into estimating a lower number of rows, explanation here:http://www-01.ibm.com/support/docview.wss?uid=swg21259831As for TABLE B, the TYPE_CODE column has about 15 different possible values and the distribution is not uniform. There are about 2.7 million rows in there and about 900k rows in A. As such, the ID1/ID2 columns in B may repeat the same value pair up to 15 times or occur only once. The arameter is no more than four values.The odd thing is that the COALESCE statement cuts the query time in half. In checking the explain plan, I noticed that several table scans on A became index scans, likely due to the lower estimate of returned rows making an index scan feasible to the optimizer. I've been digging around and trying to find a possible reason for this. RUNSTATS was very recently run on these tables and the SYSCAT.COLDIST table has some pretty recent data in it, at least within 1% of the values if I check the counts manually. I did notice that it doesn't have all of the values from the TYPE_CODE column in it, but the missing ones occur very rarely and omitting the rare values from the IN clause has no real effect on the query time. I've tried the COALESCE on other columns, but never with the same effect.The only explanation that I can think of is that some stats are stale, bad, outdated, etc, but darned if I know where to look at this point. I can say that there are no column group stats in place, but I'm not knowledgeable enough to say if that's going to make a difference. I don't want to put that COALESCE statement into production as it's a bit hacky and is more of a band-aid on a deeper problem than a solution in and of itself. It seems to me that DB2 is overestimating the number of rows that will be returned in that join, but darned if I know why. I was hoping some others with DB2 experience might be able to shed some light on this.
parameter)AND A.X IN (--subquery--)ANDCOALESCE(B.TYPE_CODE,B.TYPE_CODE) = B.TYPE_CODE\[/code\]The COALESCE does nothing from a logical standpoint and B.TYPE_CODE is never null. However, this tricks the DB2 optimizer into estimating a lower number of rows, explanation here:http://www-01.ibm.com/support/docview.wss?uid=swg21259831As for TABLE B, the TYPE_CODE column has about 15 different possible values and the distribution is not uniform. There are about 2.7 million rows in there and about 900k rows in A. As such, the ID1/ID2 columns in B may repeat the same value pair up to 15 times or occur only once. The arameter is no more than four values.The odd thing is that the COALESCE statement cuts the query time in half. In checking the explain plan, I noticed that several table scans on A became index scans, likely due to the lower estimate of returned rows making an index scan feasible to the optimizer. I've been digging around and trying to find a possible reason for this. RUNSTATS was very recently run on these tables and the SYSCAT.COLDIST table has some pretty recent data in it, at least within 1% of the values if I check the counts manually. I did notice that it doesn't have all of the values from the TYPE_CODE column in it, but the missing ones occur very rarely and omitting the rare values from the IN clause has no real effect on the query time. I've tried the COALESCE on other columns, but never with the same effect.The only explanation that I can think of is that some stats are stale, bad, outdated, etc, but darned if I know where to look at this point. I can say that there are no column group stats in place, but I'm not knowledgeable enough to say if that's going to make a difference. I don't want to put that COALESCE statement into production as it's a bit hacky and is more of a band-aid on a deeper problem than a solution in and of itself. It seems to me that DB2 is overestimating the number of rows that will be returned in that join, but darned if I know why. I was hoping some others with DB2 experience might be able to shed some light on this.