SELECT eq.MASTER_NUM,   
         ta.DV_ASSET_FLG,   
         ta.PREF_ASSET_ID_TYP,   
         ta.ASSET3,   
         ta.ASSET_NM,   
         eq.EXCH_ID,   
         eq.Q_DATE,   
         eq.USER_ID,   
         eq.ASSET_ID,   
         eq.PRC_DT,   
         ta.ASSET_TYP_CD,   
         ta.ASSET_STAT_CD,   
         trt.REASON_TYP_DESC,   
         ta.HELD_INDCTR,   
         eq.REASON,   
         T_ASSET_ASSET_REL_A.REL_ASSET_ID,   
         T_ASSET_ASSET_REL_B.ASSET_ID,   
         ta.CNTRY_QUOTE,   
         eq.VERIFY_USER_ID,   
         ta.OUT_OF_HOLD_DT,   
         ta.FIRST_DT_HELD,   
         eq.PRICING_PT_ID,
         tae.ASSET_EXT_ID_SYS_NM SECNDR_ASSET_ID_TYP_CD,
   CASE
    WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'CU' THEN ta.CUSIP
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'IS' THEN ta.ISIN
    WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'QU' THEN ta.QUICK
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'RE' THEN ta.RIC
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'SE' THEN ta.SEDOL
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'VA' THEN ta.VALOR
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'CI' THEN ta.CINS
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'LU' THEN ta.LUXEM
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'AU' THEN ta.AUSTRI
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'SV' THEN ta.SVM
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'SI' THEN ta.SICOVAM
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'WP' THEN ta.WPK
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'DU' THEN ta.DUTCH
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'CE' THEN ta.CEDEL
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'EU' THEN ta.EUROCLEAR
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'CO' THEN ta.COMMON
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'ID' THEN ta.IDC_TCKR
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'AM' THEN ta.AMS_CUSIP
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'IF' THEN ta.IFC_TCKR
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'MS' THEN ta.MSTC_CUSIP
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'SB' THEN ta.SB_CUSIP
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'MO' THEN ta.MORT_CUSIP
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'TS' THEN ta.TSE_TCKR
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'FM' THEN ta.FMSECRN
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'T1' THEN ta.ASSET1
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'T2' THEN ta.ASSET2
      WHEN atc.SECNDR_ASSET_ID_TYP_CD = 'T3' THEN ta.ASSET3
   END SECNDR_ASSET_ID,
   eg.DESCRIP AS EXCHANGE_NAME,
    ta.CNTRY_QUOTE ,
    ta.ASSET_TYP_CD ,
    ta.CNTRY_ID ,
 (select distinct tc1.CNTRY_NM from T_CNTRY tc1
   where tc1.CNTRY_ID = ta.CNTRY_QUOTE
    AND eq.ASSET_ID = ta.ASSET_ID) as CNTRY_QUOTE_NM ,
 (select distinct tc2.CNTRY_NM from T_CNTRY tc2
   where tc2.CNTRY_ID = ta.CNTRY_ID
    AND eq.ASSET_ID = ta.ASSET_ID) as CNTRY_ID_NM ,
    tat.ASSET_TYP_NM

   FROM T_PRC_ERR_Q eq,   
         T_ASSET ta    
    LEFT OUTER JOIN T_ASSET_TYP_CNTRY atc  ON ( ta.ASSET_TYP_CD = atc.ASSET_TYP_CD 
                                                AND ta.CNTRY_QUOTE = atc.CNTRY_ID 
                                                AND atc.ASSET_ID = -1 )         
    LEFT OUTER JOIN T_ASSET_ASSET_REL T_ASSET_ASSET_REL_A ON (ta.asset_id = t_asset_asset_rel_a.asset_id)
    LEFT OUTER JOIN T_ASSET_ASSET_REL T_ASSET_ASSET_REL_B ON (ta.asset_id = t_asset_asset_rel_b.rel_asset_id),  
         T_REASON_TYP trt,   
         T_BUS_GRP_CNTRY bgc,   
         T_USER tu,   
         T_PRICING_PT_GRP_ASSIGN ptg,
         T_ASSET_EXT_ID_SYS tae,
         T_EXCH_MKT mkt,
         T_EXCH_GRP eg,
         T_ASSET_TYP tat
         --t_asset_typ_cntry atc
   WHERE 
      
         ( eq.ASSET_ID = ta.ASSET_ID ) and  
         ( eq.REASON = trt.REASON_TYP_CD ) and  
         ( bgc.BUS_GRP = tu.BUS_GRP ) and  
         ( ta.CNTRY_QUOTE = bgc.CNTRY_ID ) and  
         ( tu.PRICING_PT_GRP_ID = ptg.PRICING_PT_GRP_ID ) and  
         ( ptg.PRICING_PT_ID = eq.PRICING_PT_ID ) and  
         ( trt.reason_typ_type = 'E' ) AND  
         ( tu.user_id = 2021  )    AND
         (tae.ASSET_EXT_ID_SYS_CD =  NVL(atc.SECNDR_ASSET_ID_TYP_CD,' ') or         
         tae.ASSET_EXT_ID_SYS_CD  =  NVL(atc.SECNDR_ASSET_ID_TYP_CD,'  ')) AND         
         (tae.USAGE_FLG = 'Y')  AND
         (mkt.EXCH_ID = eq.EXCH_ID) AND
         (eg.EXCH_GRP_ID = mkt.EXCH_GRP_ID)  AND
         ( ta.ASSET_TYP_CD = bgc.ASSET_TYP_CD ) AND
         ( bgc.EXCH_ID = eq.EXCH_ID ) 
    AND (tat.ASSET_TYP_CD =ta.ASSET_TYP_CD)
      AND NOT EXISTS (SELECT 1 FROM  T_EMD_ERR_Q_RULES
       WHERE ta.ASSET_TYP_CD = T_EMD_ERR_Q_RULES.ASSET_TYP_CD
       AND ta.CNTRY_QUOTE = T_EMD_ERR_Q_RULES.CNTRY_QUOTE
                            and ta.CNTRY_ID = T_EMD_ERR_Q_RULES.CNTRY_ID);

       (SELECT DISTINCT tc1.cnTry_nm
        FROM   t_cnTry tc1
        WHERE  tc1.cnTry_Id = ta.cnTry_Quote
               AND eq.Asset_Id = ta.Asset_Id) AS cnTry_Quote_nm,
       (SELECT DISTINCT tc2.cnTry_nm
        FROM   t_cnTry tc2
        WHERE  tc2.cnTry_Id = ta.cnTry_Id
               AND eq.Asset_Id = ta.Asset_Id) AS cnTry_Id_nm,

       LEFT OUTER JOIN t_Asset_typ_cnTry Atc ON (ta.Asset_typ_cd = Atc.Asset_typ_cd AND ta.cnTry_Quote = Atc.cnTry_Id AND Atc.Asset_Id = - 1)
       LEFT OUTER JOIN t_Asset_Asset_rel t_Asset_Asset_rel_a ON (ta.Asset_Id = t_Asset_Asset_rel_a.Asset_Id)
       LEFT OUTER JOIN t_Asset_Asset_rel t_Asset_Asset_rel_b ON (ta.Asset_Id = t_Asset_Asset_rel_b.rel_Asset_Id),

       AND NOT EXISTS (SELECT 1
                       FROM   t_emd_Err_q_Rules
                       WHERE  ta.Asset_typ_cd = t_emd_Err_q_Rules.Asset_typ_cd
                              AND ta.cnTry_Quote = t_emd_Err_q_Rules.cnTry_Quote
                              AND ta.cnTry_Id = t_emd_Err_q_Rules.cnTry_Id);

      indexing of join columns

AND eq.Asset_Id = ta.Asset_Id

       (SELECT DISTINCT tc1.cnTry_nm
        FROM   t_cnTry tc1
        WHERE  tc1.cnTry_Id = ta.cnTry_Quote
               AND eq.Asset_Id = ta.Asset_Id) AS cnTry_Quote_nm,

       AND (tae.Asset_ext_Id_sys_cd = Nvl(Atc.secndr_Asset_Id_typ_cd,' ') OR tae.Asset_ext_Id_sys_cd = Nvl(Atc.secndr_Asset_Id_typ_cd,'  '))