SELECT
DISTINCT
LN.LN_NBR,
BOR.MOR_NAME,
LN.ZONE,
DQY.MAN_CODE,
'DTE TRAN CODE 1578' =
(
SELECT
MAX(PMT_TXN.PMT_TRANS_DT)
FROM PMT_TXN
WHERE LN.LN_NBR = PMT_TXN.LN_NO
AND PMT_TXN.PMT_TRANS_CD IN ('148')
),
'DTE TRAN CODE 1579' =
(
SELECT
MAX(NCT.NC_TRANS_DT)
FROM NCT
WHERE LN.LN_NBR = NCT.LN_NO
AND NCT.NC_TRANS_CD = '1580'
),
'DTE TRAN CODE 1581' =
(
SELECT
MAX(NCT.NC_TRANS_DT)
FROM NCT
WHERE LN.LN_NBR = NCT.LN_NO
AND NCT.NC_TRANS_CD = '146'
)
, ORI_LN.ACQUISITION_DTE,
PROP.PROP_STATE
, S203.CK_NBR,
S203.CK_DTE,
S203.CK_DESC,
( '5/14/2019' ) AS LWD INTO CSPORTAL.CENBASE.RBYAARY05152019134446424896BF87B249D2B5C7BDA5F24D5A43a
FROM LN INNER
JOIN FE ON LN.LN_NBR = FE.LN_NBR
AND FE.FE_CODE = '2' AND FE.FE_TXN_DTE = '11/1/2018' AND FE.FE_TXN_TYPE = 'A'
INNER JOIN BOR ON LN.LN_NBR = BOR.LN_NBR
LEFT JOIN DQY ON LN.LN_NBR = DQY.LN_NBR
INNER JOIN ORI_LN ON LN.LN_NBR = ORI_LN.LN_NBR
INNER JOIN PROP ON LN.LN_NBR = PROP.LN_NBR
LEFT JOIN S203 ON FE.LN_NBR = S203.ACCT
I am getting hash match for S203.Acct tale which have 9 million rows. Any help would be appreciated.
The plan execution will have
Select into cost 0%
Table Insert 65%
compute scalar cost 0%
top cost 0%
parallelism (gather streams) cost 3%
Hash match (aggregate) cost 22%
Parallelism (Repartition streams) cost 4%
computer scalar cost 0%
Nested loops (left outer join) cost 1%
many more....
