通过改写sql的方式实现性能优化

优化前，该sql跑不出来

select
            m1.indu_code,
            m1.institution_id,
            m1.country_code,
            m1.count_month,
            nvl(a.app_num,0) as app_num,
            nvl(b.grant_num,0) as grant_num,
            now()
        from
            (select
                    a.indu_code,
                    a.institution_id,
                    country_code,
                    a.count_month
                from
                    cnpat_mid.mid_indofent_app_month_r a
            union select
                    b.indu_code,
                    b.institution_id,
                    country_code,
                    b.count_month
                from
                    cnpat_mid.mid_indofent_grant_month_r b
            ) m1 
            left join cnpat_mid.mid_indofent_app_month_r a on
            a.count_month = m1.count_month and m1.indu_code = a.indu_code and m1.institution_id = a.institution_id and a.country_code = m1.country_code 
            left join cnpat_mid.mid_indofent_grant_month_r b on
            b.count_month = m1.count_month and m1.indu_code = b.indu_code and m1.institution_id = b.institution_id and b.country_code = m1.country_code ;

优化后，该sql运行时间2分10s

select
            a.indu_code,
            a.institution_id,
            a.country_code,
            a.count_month,
            nvl(a.app_num,0) as app_num,
            nvl(b.grant_num,0) as grant_num,
            now()
        from cnpat_mid.mid_indofent_app_month_r a
        left join cnpat_mid.mid_indofent_grant_month_r b 
            on b.count_month =a.count_month and a.indu_code = b.indu_code and a.institution_id = b.institution_id and b.country_code = a.country_code 
        union 
        select
            a.indu_code,
            a.institution_id,
            a.country_code,
            a.count_month,
            nvl(b.app_num,0) as app_num,
            nvl(a.grant_num,0) as grant_num,
            now()
                from
                    cnpat_mid.mid_indofent_app_month_r b
            right join cnpat_mid.mid_indofent_grant_month_r a on
            a.count_month = b.count_month and b.indu_code = a.indu_code and b.institution_id = a.institution_id and a.country_code = b.country_code ;