这个sql怎么优化

insert  overwrite table  INTF.TT_TM_PTY_CHECK_DAY
SELECT 
 a.pd_inst_id     
,a.cust_id         
,a.pd_spec_id      
,CASE WHEN a.PD_SPEC_ID IN (9, 10, 11, 12, 14, 348, 354, 383) THEN 'BRD'
    WHEN a.PD_SPEC_ID IN  (379, 901, 804, 805) THEN 'MBL'
    WHEN a.PD_SPEC_ID IN  (881) THEN 'ITV'
    ELSE 'OTH' END   
,a.busi_st         
,a.busi_st_dt     
,a.blng_accs_nmbr
,INT(REGEXP_REPLACE(a.start_dt,'-','')) start_dt  
,INT(REGEXP_REPLACE(a.end_dt,'-','')) end_dt   
,a.blng_user_f     
,a.online_f       
,a.comp_f         
,a.exist_tp_id     
,a.chrg_f         
,a.last_chrg_f     
,d.city_cd         
,d.city_nm         
,a.agent_chnl_id   
,m.agent_chnl_nm   
,b.bss_po_spec_id
,b.bss_po_spec_nm
,a.ASSIST_CD       
,INT(REGEXP_REPLACE(a.crtd_dt,'-','')) crtd_dt         
,a.BLNG_MODE   
 FROM  (select * from bml.pd_inst_daily_info WHERE clndr_dt_id ='${hiveconf:START_TIME}')  a  
              left outer join  ( select * from bml.bss_po_spec_d gg,
                                (select max(int(regexp_replace(l.end_dt,'-',''))) end_dt2,l.po_spec_id po_spec_id2  
                                 from   bml.bss_po_spec_d l  
                                 group by l.po_spec_id) dd
                                 where int(regexp_replace(gg.end_dt,'-','')) =dd.end_dt2 and gg.po_spec_id=dd.po_spec_id2
                                )  b on a.main_po_spec_id = b.po_spec_id 
              left outer join  bml.dim_city_mpng  d on a.city_id = d.city_id
              inner join (select agent_chnl_id ,city_id,agent_chnl_nm
                          from bml.agent_chnl_d_all
                          where  (agent_chnl_nm like '%省电渠人工业务受理%' 
                                      or  agent_chnl_nm like '%省电渠苏宁易购业务受理%' 
                                      or  agent_chnl_nm like'%省电渠京东商城业务受理%' 
                                      or  agent_chnl_nm like '%省电渠网翼分销渠道%'  
                                      or  agent_chnl_no like '%省电渠天猫业务受理%' ) 
                         and  clndr_dt_id = '${hiveconf:START_MONTH}' 
                                  ) m 
                       on a.agent_chnl_id = m.agent_chnl_id
;