Here is a small testcase of the problem which I'm facing in prod env.
db2 =describe table tab1
Column Type Type
name schema name Length
Scale Null
------------------------------ --------- ------------------ --------
----- ----
-
VIN SYSIBM INTEGER 4
0 Yes
SOURCE_SYS_CODE SYSIBM INTEGER 4
0 Yes
SEQ_NUM SYSIBM INTEGER 4
0 Yes
3 record(s) selected.
db2 =select * from tab1
VIN SOURCE_SYS_CODE SEQ_NUM
----------- --------------- -----------
11 100 1
21 200 1
21 200 2
31 100 1
31 100 2
31 100 3
6 record(s) selected.
My requirement is to get only the following rows in the o/p [ select
only the combination of VIN & SOURCE_SYS_CODE which has the max seq_num
]
VIN SOURCE_SYS_CODE SEQ_NUM
----------- --------------- -----------
11 100 1
21 200 2
31 100 3
So I used the query
select VIN,SOURCE_SYS_CODE,max(seq_num)as seq_num from tab1 group by
VIN,SOURCE_SYS_CODE
However this query is behaving poorly in our prod env. Where tab1 is a
logically partitioned table on 7 partitions and VIN is the partitioning
key.
Access plan in prod is as below
Total Cost: 9.04494e+06
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
3.96437e+08
DTQ
( 2)
9.04494e+06
1.6326e+06
|
5.66339e+07
GRPBY
( 3)
8.94897e+06
1.6326e+06
|
5.66339e+07
TBSCAN
( 4)
8.94296e+06
1.6326e+06
|
5.66339e+07
SORT
( 5)
7.9601e+06
1.10822e+06
|
5.66339e+07
IXSCAN
( 6)
1.07497e+06
583829
|
5.66339e+07
INDEX: SYSIBM
Any help is greatly appreciated.
Thanks, Sam.