|
Problem IC63295
|
Status: Closed |
IMPROVEMENT ON PROCESSING FETCH FIRST N ROWS IN DPF ENVIRONMENT |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
In DPF environment, DB2 optimizer always generates an access
plan for the FETCH FIRST N ROWS clause by sending data to a
coordinator node and fetch the first N rows of the data.
This can cause performance degradation and in the access plan of
the query using FETCH FIRST N ROWS, we can see redundant TQ.
Rows
RETURN
( 1)
Cost
I/O
|
50000
DELETE
( 2)
768333
134645
/----+----\
50000 9.3116e+06
FETCH DP-TABLE: TABLE1
( 3)
390
84645
/----+-----\
50000 9.3116e+06
DTQ DP-TABLE: TABLE1
( 4)
12152.9
34645
|
1e+06
DTQ
( 5)
12088.6
34645
|
804913
TBSCAN
( 6)
11979.4
34645
|
9.3116e+06
DP-TABLE: TABLE1
This work detects cases where data are from a single node and
being applied the FETCH FIRST N ROWS clause. DB2 optimizer will,
instead of moving the data to a coordinator node, locally fetch
the first N rows from the node the date reside.
Example:
DELETE FROM (SELECT * FROM TABLE1 T WHERE DBPARTITIONNUM(T.C1) =
1 FETCH FIRST 10 ROWS ONLY);
DB2 optimizer will fetch the first 10 rows from table TABLE1 at
database partition number 1 and send the 10 rows to the DELETE
operation locally in the same database partition. |
| Problem Summary: |
IMPROVEMENT ON PROCESSING FETCH FIRST N ROWS IN DPF ENVIRONMENT |
| Local Fix: |
|
| available fix packs: |
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 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
|
| Solution |
Fixed in DB2 v97FP3 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 21.09.2009
29.09.2010
29.09.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP3
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.3
|
 |
| 9.7.0.3
|
|
