|
Problem IC84002
|
Status: Closed |
IMPROVE ACCURACY OF FILTER FACTOR ESTIMATE FOR NON-CORRELATED EXISTS CLAUSE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
The filter factor of a non-correlated EXISTS clause, such as:
...
where
exists ( select 1 from table1 A, table2 B where A.key =
b.key and A.col2 = 'XYZ')
...
May be more or less filtering depending on the results of the
subselect in the exists. This APAR will improve the accuracy of
the resulting filter factor of the EXISTS clause.
For example, you may see the exists subselect show up in the
explain plan as a FILTER with an estimated cardinality of 0.5
instead of a more accurate estimate for the probability a row
will be returned from the subselect:
/---------+
0.5
FILTER
|
/-------+--------\
1 40
TBSCAN ^NLJOIN
| |
1 /-----+------\
TABFNC: SYSIBM 40 1
GENROW TBSCAN IXSCAN
Q4 | |
1000 1000
TABLE: DB2INST1 INDEX: DB2INST1
TABLE1 TABLE2_PK
Q2 Q1 |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 9.7 FP7 or subsequent fix pack *
**************************************************************** |
| Local Fix: |
Use SELECTIVITY CLAUSE or Optimizer Profile to change the
overall access plan. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 7 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 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Problem First Fixed in DB2 Version 9.7 Fix Pack 7 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 07.06.2012
01.11.2012
01.11.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP7
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.7
|
 |
