Greetings,
I have the following query which takes very long time to execute hence
can you please help to fine tune it as I'm new to DB2 World. Thanks
SELECT
char(VEH_IDENT_NBR) as VEH_IDENT_NBR,
OPTN_CD,
SRC_TRMNT_TIMSTM,
DWH_EFCTV_TIMSTM,
DWH_UPD_TIMSTM
FROM
CARD.VIN_VEH_OPTNS a
WHERE
(
a.DWH_EFCTV_TIMSTM >
(
SELECT
MAX(DWH_ETL_ST_TIMSTM)
FROM
card.DWH_CONTROL
where
DWH_SEQ_NM = 'a' and
DWH_ETL_STATUS='S'
)
OR
a.DWH_UPD_TIMSTM >
(
SELECT
MAX(DWH_ETL_ST_TIMSTM)
FROM
card.DWH_CONTROL
where
DWH_SEQ_NM ='a' and
DWH_ETL_STATUS='S'
)
)
and
VEH_OPTN_CD_CSI=1
CARD.VIN_VEH_OPTNS Table details
- Has 58 Million rows
- Partitioned table based on VEH_IDENT_NBR
- Indexes & Keys
1. INDEX on ( DWH_UPD_TIMSTM ASC,VEH_IDENT_NBR
ASC,OPTN_CD ASC,VEH_OPTN_CD_CSI
ASC)
2. PRIMARY KEY (VEH_IDENT_NBR, OPTN_CD, VEH_OPTN_CD_CSI);
CARD.DWH_CONTROL
- Small table with 2000 rows
- Non-partitioned table
Regards,
Sam |