SQL Problem

Database
Enthusiast

SQL Problem

I execute the following SQL-1 statement. The execution time is 55 min. But the record count just have 70000 record.I modify the SQL-1 to SQL-2. The SQL-2 just first exclude the null value in RD_EXCHANGE_AVG_RATE_M200610(40 record). The execution time become 5 sec.
Why? I think V2R5 p**** engine should have some problem. Who can help me? Although I can solve by modifing SQL, I still think this is a serious bug.

SQL-1:
SELECT TXN.ACCT_NBR || TXN.CURRENCY_CODE AS ACCT_NBR
, SUM(CASE
WHEN SUBSTR( TXN.TXN_CODE , 1, 2 ) IN ( '17' , '07 ') THEN
(CASE
WHEN TXN.REVERSE_TXN_IND = 'Y' THEN -1
ELSE 1
END
)
ELSE 0 END) AS CREDIT_CNT
FROM DP_MCIF_HISTORY.EVENT_FORGN_SAV_TXN200610 TXN
LEFT JOIN DP_MCIF_HISTORY.ACCT_FORGN_SAV_CUR200610 DEP
ON TXN.ACCT_NBR = DEP.ACCT_NBR
AND TXN.CURRENCY_CODE = DEP.CURRENCY_CODE
LEFT JOIN DP_MCIF_HISTORY.RD_EXCHANGE_AVG_RATE_M200610 RD
ON TXN.CURRENCY_CODE = RD.CURRENCY_CODE
GROUP BY TXN.ACCT_NBR || TXN.CURRENCY_CODE;

SQL-2:
SELECT TXN.ACCT_NBR || TXN.CURRENCY_CODE AS ACCT_NBR
, SUM(CASE
WHEN SUBSTR( TXN.TXN_CODE , 1, 2 ) IN ( '17' , '07 ') THEN
(CASE
WHEN TXN.REVERSE_TXN_IND = 'Y' THEN -1
ELSE 1
END
)
ELSE 0 END) AS CREDIT_CNT
FROM DP_MCIF_HISTORY.EVENT_FORGN_SAV_TXN200610 TXN
LEFT JOIN DP_MCIF_HISTORY.ACCT_FORGN_SAV_CUR200610 DEP
ON TXN.ACCT_NBR = DEP.ACCT_NBR
AND TXN.CURRENCY_CODE = DEP.CURRENCY_CODE
LEFT JOIN
(sel * from DP_MCIF_HISTORY.RD_EXCHANGE_AVG_RATE_M200610 where currency is not null)RD
ON TXN.CURRENCY_CODE = RD.CURRENCY_CODE
GROUP BY TXN.ACCT_NBR || TXN.CURRENCY_CODE;
1 REPLY
Junior Contributor

Re: SQL Problem

Hi Chung-Te,
it's an Outer Join, so the result might be different, if the optimizer added that NOT NULL condition automatically.

Regarding the slow execution, could you post more details about row counts, PIs and statistics for all tables.

Btw, if this is your real query, then there's no need for Joins at all, because you don't use any column from DEP or RD.

Dieter