|
Problem IC77880
|
Status: Closed |
AN INCORRECT COST ESTIMATION BY THE OPTIMIZER CAUSES AN INCORRECT PLAN TO BE PICKED, RESULTING IN POOR QUERY PERFORMANCE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
This problem only happens when the query contains a constant in
the range predicate specification.
Below is an example of a query that will cause this problem:
SELECT * FROM MPI.FRAGMENTARY_PATIENT
WHERE (xmlexists('declare default element namespace
"urn:hl7-org:v3";
declare namespace
axolotl="urn:axolotl-com:pdo";
$c
/ClinicalDocument/recordTarget/patientRole/patient[birthTime/@va
lue[.
>= $val1 and . <= $val2]]'
PASSING PATIENT_DATA AS "c",
cast ('2010040' AS
varchar(512)) AS "val1",
cast ('2010040ZZZZZZ' AS
varchar(512)) AS "val2")
AND xmlexists('$c
/ClinicalDocument/recordTarget/patientRole/patient
[name3NameKey[(. >= $val1 and .
<= $val2) or
(.
>= $val3 and . <= $val4) or
(.
>= $val5 and . <= $val6) or
(.
>= $val7 and . <= $val8)]]'
PASSING PATIENT_DATA_OPT AS "c",
cast ('8666878962753636' AS
varchar(512)) AS "val1",
cast ('8666878962759090' AS
varchar(512)) AS "val2",
cast ('8988766263787062' AS
varchar(512)) AS "val3",
cast ('8988766263787066' AS
varchar(512)) AS "val4",
cast ('8666878736363636' AS
varchar(512)) AS "val5",
cast ('8666878736369090' AS
varchar(512)) AS "val6",
cast ('8666363636363636' AS
varchar(512)) AS "val7",
cast ('8666363636363647' AS
varchar(512)) AS "val8")) |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users on V9.7 FP4 and below *
****************************************************************
* PROBLEM DESCRIPTION: *
* AN INCORRECT COST ESTIMATION BY THE OPTIMIZER CAUSES AN *
* INCORRECT PLAN TO BE PICKED, RESULTING IN POOR QUERY *
* PERFORMANCE: *
* *
* This problem only happens when the query contains a constant *
* in *
* the range predicate specification. *
* Below is an example of a query that will cause this *
* problem: *
* *
* SELECT * FROM MPI.FRAGMENTARY_PATIENT *
* WHERE (xmlexists('declare default element namespace *
* "urn:hl7-org:v3"; *
* declare namespace *
* axolotl="urn:axolotl-com:pdo"; *
* $c *
* /ClinicalDocument/recordTarget/patientRole/patient[birthTime *
* /@va *
* lue[. *
* >= $val1 and . <= $val2]]' *
* PASSING PATIENT_DATA AS "c", *
* cast ('2010040' AS *
* varchar(512)) AS "val1", *
* cast ('2010040ZZZZZZ' AS *
* varchar(512)) AS "val2") *
* AND xmlexists('$c *
* /ClinicalDocument/recordTarget/patientRole/patient *
* [name3NameKey[(. >= $val1 *
* and . *
* <= $val2) or *
* *
* (. *
* >= $val3 and . <= $val4) or *
* *
* (. *
* >= $val5 and . <= $val6) or *
* *
* (. *
* >= $val7 and . <= $val8)]]' *
* PASSING PATIENT_DATA_OPT AS *
* "c", *
* cast ('8666878962753636' AS *
* varchar(512)) AS "val1", *
* cast ('8666878962759090' AS *
* varchar(512)) AS "val2", *
* cast ('8988766263787062' AS *
* varchar(512)) AS "val3", *
* cast ('8988766263787066' AS *
* varchar(512)) AS "val4", *
* cast ('8666878736363636' AS *
* varchar(512)) AS "val5", *
* cast ('8666878736369090' AS *
* varchar(512)) AS "val6", *
* cast ('8666363636363636' AS *
* varchar(512)) AS "val7", *
* cast ('8666363636363647' AS *
* varchar(512)) AS "val8")) *
****************************************************************
* RECOMMENDATION: *
* Update to FixPack 5 or higher *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
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 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 |
First Fixed in DB2 V9.7 FP5 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 02.08.2011
21.12.2011
21.12.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
