|
Problem IC64120
|
Status: Closed |
Performance Regression due to join between two or more tables with group by clause on inner table and fetch first N row |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
A performance regression has been noticed.
The access plan has the following characteristics:
NLJOIN
|
+---------+---------+
<ANY> GRPBY
|
TBSCAN
1. There is an Nested Loop Join on at least two tables.
2. The inner table of the Nested Loop join is a Group By
operator
3. No eligible index(es) on the inner table.
4. Local predicate(s) applied on the inner table
5. Fetch First N Rows is specified above the join.
Inner table refers to the table used as the inner leg of a join
in the access plan generated by the Optimizer
Example:
select * from T1, (select x from T2 where y = 'NAME' group by x)
where T1.x = T2.x order by T2.x fetch first 200 rows
Recommended Workaround:
1. create index ix1 on T2(x)
2. RUNSTATS ON TABLE <schema>.T2 FOR INDEXES ALL |
| Problem Summary: |
USERS AFFECTED: All
PROBLEM DESCRIPTION:
see ERROR DESCRIPTION
PROBLEM SUMMARY:
see ERROR DESCRIPTION |
| Local Fix: |
Create qualified indexes on the all join columns of the inner
table and perform runstats. |
| available fix packs: |
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 6 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 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 |
The complete fix for this problem first appears in DB2 UDB
Version 9.7 FixPak 2. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 26.10.2009
18.05.2010
18.05.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.2
|
 |
