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....