DB2 - Problembeschreibung
Problem IC88354 | Status: Geschlossen |
ADJUST FILTER FACTOR FOR QUERIES WITH HOST VARIABLES AGAINST TABLES WITH CONSTRAINTS | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 | |
Problembeschreibung: | |
A query that includes host variables against a table with constraints can have a check added to the optimized SQL to validate the host variables are within the proper range of the constraints. This provides an early out and prevents the query from running against the actual tables if the values would not return any rows. This operator does not filter any rows in the actual output. However, the filter factor estimate for this operator reduces the estimated cardinality of the results, causing the access plan to underestimate the actual cost. For instance, the following example shows the GENROW filter factor results in a estimate of 0.111111 rows. Applied to the base table, that reduces the estimate from 40 rows down to 4.44444 rows. Since this operator does not actually filter any rows, so the filter factor should be 1 and keeping the estimate of 40 rows. CREATE TABLE DB2INST1.TEST1 (COL1 INT, COL2 INT); ALTER TABLE DB2INST1.TEST1 ADD CONSTRAINT COL1_CHK CHECK (COL1 BETWEEN 1 AND 4) ENFORCED ENABLE QUERY OPTIMIZATION; ALTER TABLE DB2INST1.TEST1 ADD CONSTRAINT COL2_CHK CHECK (COL2 BETWEEN 0 AND 127) ENFORCED ENABLE QUERY OPTIMIZATION; Original Statement: ------------------ select col2 from db2inst1.test1 where col1 = ? Optimized Statement: ------------------- SELECT Q3.COL2 AS "COL2" FROM (SELECT Q1.$C0 FROM (VALUES 0) AS Q1 WHERE (? < 127) AND (0 < ?)) AS Q2, DB2INST1.TEST1 AS Q3 WHERE (Q3.COL1 = ?) Rows RETURN ( 1) Cost I/O | 4.44444 NLJOIN ( 2) 15.646 2 /-----+------\ 0.111111 40 TBSCAN TBSCAN ( 3) ( 4) 0.00025113 15.6458 0 2 | | 1 1000 TABFNC: SYSIBM TABLE: DB2INST1 GENROW TEST1 Q1 Q3 | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 V10.1 Fix Pack 2 * **************************************************************** | |
Local-Fix: | |
verfügbare FixPacks: | |
DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows | |
Lösung | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 17.11.2012 18.12.2012 18.12.2012 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
10.1.0.2 | |
10.5.0.2 |