Need to use USING keyword in the query - REWRITE

Database
Enthusiast

Need to use USING keyword in the query - REWRITE

 

Dear experts,

 

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 ;

 

 

 

2 REPLIES
Senior Apprentice

Re: Need to use USING keyword in the query - REWRITE

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? 

Enthusiast

Re: Need to use USING keyword in the query - REWRITE

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.