question

guruswak avatar image
guruswak asked

Hash Match

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

sql-server-2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.