Select Query - Looking for a better query

[Sam Durai]

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_nu m)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.

[Ian]

Sam Durai wrote:

Here is a small testcase of the problem which I'm facing in prod env.

[...]

So I used the query
select VIN,SOURCE_SYS_ CODE,max(seq_nu m)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.

Not sure why you think the query is *behaving* poorly. With the
information you provided there aren't really any options for a
better access path.

Poor performance is a different matter, and may be related to things
like physical tablespace design (esp. the system temp tablespace),
bufferpools, or sort memory (you are sorting approx 56M rows x 16
bytes).

[Tonkuma]

I'm not sure. But, followings might be worth trying.
Because, I expect the possibility that DB2 first get result pairs of
(Vin, Seq_num) only by using the Index. Then get directly corresponding
Source_sys_code value from the table.
So, table scan can be eliminated.

CREATE UNIQUE INDEX tab1_1 ON tab1
(Vin, Seq_num);

SELECT Vin, Source_sys_code , Seq_num
FROM (SELECT Vin, Source_sys_code , Seq_num
, MAX(Vin) OVER(ORDER BY Vin, Seq_num
ROWS BETWEEN 1 FOLLOWING
AND 1 FOLLOWING) Next_Vin
FROM tab1) Q
WHERE Vin <Next_Vin
OR Next_Vin IS NULL
;

[Tonkuma]

I'm sorry very much confusing you.
Please forget my previous post.
I didn't read following condition.

Where tab1 is a
logically partitioned table on 7 partitions and VIN is the partitioning
key.