|
Problem IC91738
|
Status: Closed |
PREDICATE SELECTIVITY CLAUSE MAY NOT TAKE EFFECT IN LEFT OUTER JOIN QUERY |
| product: |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 |
| Problem description: |
Under registry DB2_SELECTIVITY 'YES' setting, user can specify
the selectivity of a predicate and optimizer will apply it
instead of its estimated version as predicate filter factor. The
"SELECTIVITY" keyword and its value are expected to be present
at "Optimized Statement" as well as "Plan Details" section in
db2exfmt output plan file. If they are missed, it indicates
the user specified selectivity is lost during query compilation
and optimizer doesn't make use of it. This type of problem can
happen to query statement with mixed outer join and inner join.
Assume the outer join predicate is R.x = N.x and inner join
predicate is R.y = I.y. The problem requires all the following
conditions to be satisfied:
1. I.y is unique.
2. The SELECTIVITY clause is appended after a simple local
equality predicate on table R. That predicate should be an
equality predicate between simple base column and a literal. It
can't involve any expression. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All users on DB2 V101 FP2 and earlier *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 V101 FP2 *
**************************************************************** |
| Local Fix: |
Manually rewrite local equality predicate by replacing <literal>
in "R.z = <literal>" with an equivalent expression, e.g. max(?,
cast(null as int)).
Ex:
original query predicate ---> Table1.Column1 = ? selectivity
.000001
rewritten query predicate ----> TTable1.Column1=max(?,0)
selectivity .000001 |
| available fix packs: |
DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
|
| Solution |
First Fixed in DB2 V101 FP2 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 22.04.2013
07.10.2013
07.10.2013 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.1.0.3
|
 |
| 10.1.0.3
|
|
