|
Problem IC62710
|
Status: Closed |
DB2 QUERY REWRITE MAY NOT TAKE COMPLETE ADVANTAGE OF REFERENTIAL INTEGRITY TO OPTIMIZE A QUERY |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Queries containing joins based on referential integrity (RI)
defined in the database, i.e., primary, unique and foreign keys
(PK, UK and FK), are used by the DB2 Query ReWrite engine (QRW)
to optimize the query graph (QGM) for optimal query performance.
.
This usually allows dropping of PK tables from joins used in the
query. However, presence of certain constructs in the query may
cause QRW to optimize the QGM in such a way that PK tables that
are droppable given the RI may not be dropped, and consequently
may adversely affect query performance.
.
For example, consider the following scenario:
-- Table and Primary Key DDL
CREATE TABLE SON (DAD_ID INTEGER NOT NULL
CONSTRAINT PK_SON PRIMARY KEY,
-- PK
MOM_ID INTEGER NOT NULL,
ID INTEGER NOT NULL WITH DEFAULT 0,
AGE INTEGER NOT NULL WITH DEFAULT 0
);
.
CREATE TABLE MOM (ID INTEGER NOT NULL
CONSTRAINT PK_MOM PRIMARY KEY
-- PK
);
.
CREATE TABLE DAD (ID INTEGER NOT NULL
CONSTRAINT PK_DAD PRIMARY KEY,
-- PK
ANCSTR_ID INTEGER NOT NULL
);
.
CREATE TABLE GRANDPA (ID INTEGER NOT NULL
CONSTRAINT PK_GRANDPA PRIMARY KEY
-- PK
);
.
CREATE TABLE CHILDREN_OF (ID INTEGER NOT NULL);
.
-- Foreign Key DDL
ALTER TABLE SON
ADD CONSTRAINT FK_SON_MOM FOREIGN KEY (MOM_ID)
REFERENCES MOM (ID)
ON DELETE CASCADE
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
.
ALTER TABLE SON
ADD CONSTRAINT FK_SON_DAD FOREIGN KEY (DAD_ID)
REFERENCES DAD (ID)
ON DELETE CASCADE
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
.
ALTER TABLE DAD
ADD CONSTRAINT FK_DAD_GRANDPA FOREIGN KEY (ANCSTR_ID)
REFERENCES GRANDPA (ID)
ON DELETE CASCADE
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
.
ALTER TABLE CHILDREN_OF
ADD CONSTRAINT FK_CHILDRENOF_GP FOREIGN KEY (ID)
REFERENCES GRANDPA (ID)
ON DELETE CASCADE
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
.
-- Query
SELECT SON.AGE
FROM SON,
MOM,
DAD,
GRANDPA
WHERE SON.ID = 1
AND SON.MOM_ID = MOM.ID
AND SON.DAD_ID = DAD.ID
AND DAD.ANCSTR_ID = GRANDPA.ID
AND GRANDPA.ID IN ( SELECT CHILDREN_OF.ID
FROM CHILDREN_OF
);
.
Due to the presence of the IN subquery predicate, QRW alters the
QGM in such a way that the droppable PK tables in the query are
not dropped (this can be determined by looking at the optimized
statement section of the formatted EXPLAIN output of the query).
In this case, these are the GRANDPA and MOM tables.
.
The enhancement in this APAR will attempt to address such issues
on a best-effort basis by giving extra special consideration to
the RI when altering the QGM in QRW. |
| Problem Summary: |
Users Affected : All
Problem Description :
DB2 Query Rewrite may not take complete advantage of referential
integrity to optimize a query.
Problem Summary :
Queries containing joins based on referential integrity (RI)
defined in the database, i.e., primary, unique and foreign keys
(PK, UK and FK), are used by the DB2 Query ReWrite engine (QRW)
to optimize the query graph (QGM) for optimal query performance.
This usually allows dropping of PK tables from joins used in the
query. However, presence of certain constructs in the query may
cause QRW to optimize the QGM in such a way that PK tables that
are droppable given the RI may not be dropped, and consequently
may adversely affect query performance.
For example, consider the following scenario:
-- Table and Primary Key DDL
CREATE TABLE SON (DAD_ID INTEGER NOT NULL
CONSTRAINT PK_SON PRIMARY KEY,
-- PK
MOM_ID INTEGER NOT NULL,
ID INTEGER NOT NULL WITH DEFAULT 0,
AGE INTEGER NOT NULL WITH DEFAULT 0
);
CREATE TABLE MOM (ID INTEGER NOT NULL
CONSTRAINT PK_MOM PRIMARY KEY
-- PK
);
CREATE TABLE DAD (ID INTEGER NOT NULL
CONSTRAINT PK_DAD PRIMARY KEY,
-- PK
ANCSTR_ID INTEGER NOT NULL
);
CREATE TABLE GRANDPA (ID INTEGER NOT NULL
CONSTRAINT PK_GRANDPA PRIMARY KEY
-- PK
);
CREATE TABLE CHILDREN_OF (ID INTEGER NOT NULL);
.
-- Foreign Key DDL
ALTER TABLE SON
ADD CONSTRAINT FK_SON_MOM FOREIGN KEY (MOM_ID)
REFERENCES MOM (ID)
ON DELETE CASCADE
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
ALTER TABLE SON
ADD CONSTRAINT FK_SON_DAD FOREIGN KEY (DAD_ID)
REFERENCES DAD (ID)
ON DELETE CASCADE
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
ALTER TABLE DAD
ADD CONSTRAINT FK_DAD_GRANDPA FOREIGN KEY (ANCSTR_ID)
REFERENCES GRANDPA (ID)
ON DELETE CASCADE
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
ALTER TABLE CHILDREN_OF
ADD CONSTRAINT FK_CHILDRENOF_GP FOREIGN KEY (ID)
REFERENCES GRANDPA (ID)
ON DELETE CASCADE
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
-- Query
SELECT SON.AGE
FROM SON,
MOM,
DAD,
GRANDPA
WHERE SON.ID = 1
AND SON.MOM_ID = MOM.ID
AND SON.DAD_ID = DAD.ID
AND DAD.ANCSTR_ID = GRANDPA.ID
AND GRANDPA.ID IN ( SELECT CHILDREN_OF.ID
FROM CHILDREN_OF
);
Due to the presence of the IN subquery predicate, QRW alters the
QGM in such a way that the droppable PK tables in the query are
not dropped (this can be determined by looking at the optimized
statement section of the formatted EXPLAIN output of the query).
In this case, these are the GRANDPA and MOM tables.
The enhancement in this APAR will attempt to address such issues
on a best-effort basis by giving extra special consideration to
the RI when altering the QGM in QRW. |
| Local Fix: |
No general workaround exists, however, potential workarounds may
be available on a query by query basis. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 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 10 for Linux, UNIX, and Windows
|
| Solution |
Problem was first fixed in V9.7 FP1. |
| Workaround |
No general workaround exists, however, potential workarounds may
be available on a query by query basis. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 21.08.2009
15.12.2009
15.12.2009 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
