DB2 - Problembeschreibung
Problem IT06857 | Status: Geschlossen |
REDUNDANT JOINS MIGHT NOT BEING PRUNED WITH A SELF JOIN QUERY ON VIEW | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problembeschreibung: | |
The DB2 optimizer query rewrite component might not remove all possible redundant joins when a self join query is done within a view. This issue can be recreated as following: create table A (A int not null primary key, X int not null) organize by column; create table B (B int not null primary key, A int not null references A (A) not enforced enable query optimization) organize by column; create table F (B int not null references B(B) not enforced enable query optimization, C int not null, I int not null, Y int not null) organize by column; create or replace view BV as select B.*, A.X from B inner join A on B.A = A.A; create or replace view V as select F.*, BV.A, BV.X from F inner join BV on F.B = BV.B; and this query: explain plan for select SUM(Y) from V where C IN (select C from V where I IN (1,2)) and I NOT IN (1,2); In the explain for the above select, the access plan should only touch table F, but it also joins to B. Original Statement: ------------------ select SUM(Y) from V where C IN (select C from V where I IN (1, 2) ) and I NOT IN (1, 2) Optimized Statement: ------------------- SELECT Q5.$C0 FROM (SELECT SUM(Q4.Y) FROM (SELECT DISTINCT Q2.Y, Q1.A, Q2.B, Q2.$P-ROWID$ FROM DB2V1055.B AS Q1, DB2V1055.F AS Q2, DB2V1055.F AS Q3 WHERE (Q2.B = Q1.B) AND (Q2.C = Q3.C) AND Q3.I IN (1, 2) AND NOT(Q2.I IN (1, 2)) ) AS Q4 ) AS Q5 | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Problem Description above. * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 10.5 Fix Pack 7. * **************************************************************** | |
Local-Fix: | |
Lösung | |
First fixed in DB2 Version 10.5 Fix Pack 7. | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 30.01.2015 21.01.2016 21.01.2016 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
10.5.0.7 |