|
Problem IC67537
|
Status: Closed |
IMPROVEMENT ON PROCESSING FETCH FIRST N ROWS IN DPF ENVIRONMENT |
| product: |
DB2 FOR LUW / DB2FORLUW / 980 - 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: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* IMPROVEMENT ON PROCESSING FETCH FIRST N ROWS IN DPF *
* ENVIRONMENT *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 V98FP4 *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Version 9.8 Fix Pack 4 for AIX and Linux
DB2 Version 9.8 Fix Pack 5 for AIX and Linux
|
| Solution |
Fixed in DB2 V98 FP4 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 01.04.2010
05.08.2011
05.08.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.8.FP4
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.8.0.4
|
 |
