I am trying to re-write the below query using the USING keyword.
I have tried multiple methods to improve the performance but no luck.
SyntaxEditor Code Snippet
SELECT BIN , CARD_TYPE , T1.ISSUING_BANK_NM , TRANS_TYPE , NO_OF_TRANS , Sum_of_Trans FROM ( SELECT B.CARD_BIN_NBR AS BIN , B.CARD_TYP AS CARD_TYPE , C.XACT_CATGY_NM AS TRANS_TYPE , COUNT ( B.XACT_LOC_TS ) AS NO_OF_TRANS , SUM ( B.ACT_SETTLE_AMT ) AS Sum_of_Trans FROM SPS_OI.POST_XACT_CUST A , SPS_OI.POST_XACT B , SPS_OI.XACT_TYP_LKP C WHERE ( A.GUID_XACT_REF_NBR = B.GUID_XACT_REF_NBR ) AND ( B.XACT_TYP = C.XACT_TYP ) AND B.MSG_TYP IN ( '0200' , '0220' ) AND B.CARD_BIN_NBR IS NOT NULL AND CAST( CAST( CAST( B.XACT_LOC_TS AS FORMAT 'YYYYMMDD' ) AS CHAR ( 8 ) ) AS DATE FORMAT 'YYYYMMDD' ) BETWEEN '20161201' AND '20161231' GROUP BY 1 , 2 , 3) ABC LEFT OUTER JOIN (SELECT ISSUING_BANK_NM, SUBSTR ( to_char( E.BIN_LOW_NBR ) ,1 , 6 ) as BLN, SUBSTR ( to_char( E.BIN_HIGH_NBR ) , 1 ,6 ) as BHN FROM SPS_OI.FD_GBL_BIN_FILE E ) T1 ON ABC.BIN = T1.BLN OR T1.BHN = ABC.BIN GROUP BY 1 , 2 , 3 , 4, 5, 6 ;
What do you mean by re-write using the USING keyword?
USING is for prepared SQL statetments.
The biggest problem is probably the ORed join condition resulting in a Product Join.
Can you share the DDL & Explain?
Yes, the problem was with the Product join happened due to OR join condition.
I tried using the CTE (Common Table Expression) and was able to achieve a great performance.
Thanks for your inputs.