The DB2 Query optimizer can use a faster method called Greedy
for determining the order in which to join the tables referenced
in the query when the query contains many join operations. This
APAR is only applicable when using Greedy join enumeration. If
the statement was compiled with query optimization levels 0, 1,
or 2, or if you received a SQL0437W reason code 1 or reason code
2, then the query was compiled using Greedy join enumeration.
Additionally if you have the DB2_REDUCED_OPTIMIZATION registry
variable set, then we can choose to use Greedy join enumeration
under certain circumstances.
The Greedy method favours the joins with the lowest cost and
does not consider higher cost alternatives which may lead to a
better performance. When the query contains many tables being
joined with many transitive join predicates this enumeration
method can lead to a less-than-optimal plan selection. For
example in the following query
SELECT a1, a2, a3, b2, b3
FROM A,B,C,D
WHERE a1=b1
AND b1=c1
AND d2=a2
In this query there is an implicit join predicate a1 = c1. As a
result the optimizer could consider an initial join of table A
to any of B or C or D. If the join of A and B is the least
expensive join it will be chosen using this faster join planning
method, and once this is chosen we will no longer consider a
join of A with C or A with D even though those might lead to a
lower cost plan overall. For example if the join of A with B
results in 1,000,000 rows but is very inexpensive and the join A
with D result in 100 rows but it very expensive, the faster join
planning method will chose the join A with B even though that
means processing a very large number of rows.
The problem addressed by this APAR is most often characterized
by a series of joins using only the predicates 'a1=b1 and a1=c1
and b1=c1' with rapidly increasing cardinality estimates shown
in the plan. Eventually the join to table D will occur and the
cardinality estimate will drop.
This APAR will enable the optimizer to choose a better join
order under the specified conditions. The behaviour change is
enabled using either of the following settings:
The setting
DB2_EXTENDED_OPTIMIZATION=GY_DELAY_EXPAND
Causes the faster join planning method to delay adding expanding
joins until all the filtering and lookup joins have been added
to the plan. In practical terms this means that the intermediate
results sizes of the joins will not grow beyond the largest
table involved in the join until the optimizer has no other
choice. This prevent us from generating the huge intermediate
result set, but the intermediate result set is still the size of
the largest table, until it becomes cheapest to join in the
filtering table(s).
The risk of this setting, since planning is not strictly based
on cost estimates, is that we are limiting our choices and thus
may be forced into more expensive joins.
or
DB2_EXTENDED_OPTIMIZATION=GY_DELAY_LOOKUP
Causes the greedy join enumeration algorithm to delay adding
expanding and lookup joins until all the filtering joins have
been added to the plan. What this means is that we will try to
generate a plan that does the filtering as early as possible,
thus shrinking the result set early.
The risk of this setting, since planning is not strictly based
on cost estimates, is that we are limiting our choices and thus
may be forced into more expensive joins. The GY_DELAY_LOOKUP
permits fewer choices then the GY_DELAY_EXPAND mode. |
If you are experiencing this problem you can work around the
problem using the following methods:
1) Use an optimization profile to indicate the first join should
be between A and D
2) If you are using query optimization level 0, 1 or 2, then
attempt to compile the statement using optimization level 3 or
higher so that we use the Dynamic Programming join enumeration
method.
3) If you have received a SQL0437W reason code 1 then attempt to
increase the value of the STMTHEAP configuration parameter to
give more memory to Dynamic Programming join enumeration.
4) If you have DB2_REDUCED_OPTIMIZATION set then you may want to
attempt to unset and determine if that helps the problem. |
