|
Problem IC77880
|
Status: Geschlossen |
AN INCORRECT COST ESTIMATION BY THE OPTIMIZER CAUSES AN INCORRECT PLAN TO BE PICKED, RESULTING IN POOR QUERY PERFORMANCE |
| Produkt: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problembeschreibung: |
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-Zusammenfassung: |
****************************************************************
* 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: |
|
| verfügbare FixPacks: |
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
|
| Lösung |
First Fixed in DB2 V9.7 FP5 |
| Workaround |
keiner bekannt / siehe Local-Fix |
| Weitere Daten |
Datum - Problem gemeldet :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
| 02.08.2011
21.12.2011
21.12.2011 |
| Problem behoben ab folgender Versionen (IBM BugInfos) |
9.7.FP5
|
| Problem behoben lt. FixList in der Version |
| 9.7.0.5
|
 |
