Guys, Can you please help me to tune this SQL. Env : DB2 8.2 on AIX. Query references 2 partitoned tables (GV & GVE is spread across 7 logical parititions)
SELECT
char(GV.VEH_IDENT_NBR) AS VEH_IDENT_NBR,
substr(GV.VIN_TYPE_CD,1,1) AS VIN_TYPE_CD,
char(date(GV.TRANSMIT_DT),ISO) AS TRANSMIT_DT,
GVE.SHIP_MODE_CD,
FROM
GLOBAL_VEH GV
LEFT OUTER JOIN
(
SELECT
B.VEH_IDENT_NBR,
B.SHIP_MODE_CD
FROM
(
SELECT
VEH_IDENT_NBR,
SHIP_MODE_CD,
MAX(EVNT_SEQ_NBR)
FROM
CARD.GLOBAL_VEH_EVENT
WHERE
VEH_EVNT_CD='4200'
GROUP BY
VEH_IDENT_NBR,
SHIP_MODE_CD
) AS B
) AS GVE
ON
GV.VEH_IDENT_NBR=GVE.VEH_IDENT_NBR
WHERE
(
GV.DWH_EFCTV_TIMSTM >
(
SELECT
MAX(C.DWH_ETL_ST_TIMSTM)
FROM
DWH_CONTROL C
WHERE
AND C.DWH_ETL_STATUS='S'
)
OR
GV.DWH_UPD_TIMSTM >
(
SELECT
MAX(C.DWH_ETL_ST_TIMSTM)
FROM
DWH_CONTROL C
WHERE
AND C.DWH_ETL_STATUS='S'
)
)
Access Plan is as below.
Rows
RETURN
( 1)
Cost
I/O
|
83937.8
DTQ
( 2)
44216.3
17846.3
|
27979.2
GRPBY
( 3)
44190.6
17846.3
|
27979.2
TBSCAN
( 4)
44187.2
17846.3
|
27979.2
SORT
( 5)
44183.9
17846.3
|
27979.2
FETCH
( 6)
44139.4
17846.3
/----+---\
335751 335751
RIDSCN TABLE: CARD
( 7) GLOBAL_VEH_EVENT
1804.76
597.209
|
335751
SORT
( 8)
1804.76
597.209
|
335751
IXSCAN
( 9)
1446.51
597.209
|
335751
INDEX: CARD30
G_VEH_ENT_LAAMUI
dBforums
>
Query Tuning Help
05-01-06, 13:57
Linear Mode
