|
Problem IC65232
|
Status: Closed |
OPTIMIZER MIGHT NOT CHOOSE AN IN-TO-JOIN ACCESS WITHIN A STAR JO IN INDEX ANDING ACCESS WHEN AN OPTIMIZATION GUIDELINE IS PRESENT |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
If you issue a query with an IN-list predicate and apply a
statement-level optimization guideline to the query, and if the
chosen access plan has a star join index-ANDing access with the
IN-list predicate applied in one of the star join semi-join
subplans, then the optimizer might not choose an in-to-join
access for applying the IN-list predicate even if that would
have been the optimal choice. As a result, the query might not
perform as well as expected.
This might occur whether or not the optimization guideline
itself includes a join request for the in-to-join access or a
star join index ANDing access request.
You might or might not receive an SQL0437W warning sqlcode
("Performance of this complex query may be sub-optimal.") with
reason code 13 when this occurs.
Since the Access Plan Reuse feature makes use of optimization
guideline functionality, this might also result in Access Plan
Reuse not being fully successful. If this occurs you will
receive an SQL20516W warning sqlcode ("The statement compilation
was successful but the access plan for this statement could not
be preserved.") with reason code 107. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* Under some circumstances the query optimizer will fail to *
* consider in-to-join access plans for applying IN-list *
* predicates within a star join index-ANDing semijoin subplan *
* when a statement-level optimization guideline is present. As *
* a result, the generated access plan may be suboptimal. *
* *
* You might or might not receive an SQL0437W warning sqlcode *
* ("Performance of this complex query may be sub-optimal.") *
* with reason code 13 when this occurs. *
* *
* *
* *
* Since the Access Plan Reuse feature makes use of *
* optimization guideline functionality, this might also result *
* in Access Plan Reuse not being fully successful. If this *
* occurs you will receive an SQL20516W warning sqlcode ("The *
* statement compilation was successful but the access plan for *
* this statement could not be preserved.") with reason code *
* 107. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Version 9.7 Fix Pack 2. *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Problem was first fixed in Version 9.7 Fix Pack 2. The fix is to
be applied to the database server. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 18.12.2009
14.05.2010
14.05.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP2
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.2
|
 |
