急急急两个子查询连接的语句查询效率非常慢

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。
【DM版本】:DM8
【操作系统】：centOS
【CPU】:16
【问题描述】*：相同的数据库表索引和结构，把sql从pgsql适配到达梦后效率非常低
SELECT
/*+enable_hash_join(0)*
          /*+GROUP_OPT_FLAG(1)*/
          '鄂尔多斯市' AS area_name,
          '150600000000' AS area_code,
          A.date_type,
          A.start_time,
          A.end_time,
          A.scheme_code,
          A.product_name,
          A.product_code,
          A.efficiency_name AS efficiency_name,
          A.indicator_name,
          A.indicator_code,
          A.data_caliber,
          A.category,
          CASE C.indicator_value WHEN 0 THEN 0 ELSE A.indicator_value / C.indicator_value END AS indicator_value,
          A.unit,
          A.data_source,
          CASE C.indicator_value WHEN 0 THEN '分母为零' ELSE'' END AS standby1
     FROM ( SELECT
                    /*+GROUP_OPT_FLAG(1)*/
                    B.area_name,
                    B.area_code,
                    3 AS date_type,
                    A.start_time,
                    A.end_time,
                    '00000' AS scheme_code,
                    A.product_name,
                    A.product_code,
                    B.efficiency_name AS efficiency_name,
                    B.indicator_name,
                    '' AS indicator_code,
                    '当量值' AS data_caliber,
                    A.category,
                    SUM ( A.indicator_value * b.indicator_value ) AS indicator_value,
                    B.unit,
                    'energy_k103_2,k216_2_enterprise_product_energy_standard' AS data_source
               FROM energy_data_model.k216_2_enterprise_product_energy_standard AS B
          LEFT JOIN energy_data_model.energy_k103_2 AS A 
                 ON A.area_code = B.area_code AND A.enterprise_code = B.enterprise_code AND A.product_code = B.product_code AND A.indicator_name = '产量' AND A.data_caliber = '实物量' AND A.date_type = '3' AND A.category = '1-本期值'
              WHERE B.indicator_name IN ( '单位产品综合能耗', 
                                         '供电煤耗', 
                                         '综合供热煤耗' ) AND A.product_code IS NOT NULL AND A.start_time >= '2020-01-01' AND A.end_time <= '2023-12-31' AND SUBSTRING ( '150600000000', 5, 12 ) = 0 AND SUBSTRING ( A.area_code, 5, 12 ) > 0 AND SUBSTRING ( '150600000000', 1, 4 ) = SUBSTRING ( A.area_code, 1, 4 )
           GROUP BY A.product_code,
                    A.date_type,
                    A.start_time,
                    A.end_time,
                    A.indicator_name,
                    A.data_caliber,
                    A.category,
                    B.efficiency_name ) AS A 
LEFT JOIN ( SELECT
                    /*+GROUP_OPT_FLAG(1)*/
                    A.area_name,
                    A.area_code,
                    A.date_type,
                    A.start_time,
                    A.end_time,
                    A.product_name,
                    A.product_code,
                    B.efficiency_name AS efficiency_name,
                    A.indicator_name,
                    A.indicator_code,
                    A.data_caliber,
                    A.category,
                    SUM ( A.indicator_value ) AS indicator_value,
                    B.unit,
                    A.data_source
               FROM energy_data_model.k216_2_enterprise_product_energy_standard AS B
          LEFT JOIN energy_data_model.energy_k103_2 AS A 
                 ON A.area_code = B.area_code AND A.enterprise_code = B.enterprise_code AND A.product_code = B.product_code AND A.indicator_name = '产量' AND A.data_caliber = '实物量' AND A.date_type = 3 AND A.category = '1-本期值'
              WHERE B.indicator_name IN ( '单位产品综合能耗', 
                                         '供电煤耗', 
                                         '综合供热煤耗' ) AND A.product_code IS NOT NULL AND A.start_time >= '2020-01-01' AND A.end_time <= '2023-12-31' AND SUBSTRING ( '150600000000', 5, 12 ) = 0 AND SUBSTRING ( A.area_code, 5, 12 ) > 0 AND SUBSTRING ( '150600000000', 1, 4 ) = SUBSTRING ( A.area_code, 1, 4 )
           GROUP BY A.product_code,
                    A.date_type,
                    A.start_time,
                    A.end_time,
                    A.indicator_name,
                    A.data_caliber,
                    A.category,
                    B.efficiency_name ) AS C 
       ON A.product_code = C.product_code AND A.efficiency_name = C.efficiency_name AND A.date_type = C.date_type AND A.start_time = C.start_time AND A.end_time = C.end_time AND C.indicator_name = '产量' AND C.data_caliber = '实物量' AND C.date_type = 3 AND C.category = '1-本期值'
    WHERE A.product_code IS NOT NULL 
UNION ALL
   SELECT/*+enable_hash_join(0)*
          /*+GROUP_OPT_FLAG(1)*/
          A.area_name,
          A.area_code,
          A.date_type,
          A.start_time,
          A.end_time,
          A.scheme_code,
          A.product_name,
          A.product_code,
          A.efficiency_name AS efficiency_name,
          A.indicator_name,
          A.indicator_code,
          A.data_caliber,
          A.category,
          CASE C.indicator_value WHEN 0 THEN 0 ELSE A.indicator_value / C.indicator_value END AS indicator_value,
          A.unit,
          A.data_source,
          CASE C.indicator_value WHEN 0 THEN '分母为零' ELSE'' END AS standby1
     FROM ( SELECT
                    /*+GROUP_OPT_FLAG(1)*/
                    A.area_name,
                    A.area_code,
                    3 AS date_type,
                    A.start_time,
                    A.end_time,
                    '00000' AS scheme_code,
                    A.product_name,
                    A.product_code,
                    B.efficiency_name,
                    B.indicator_name,
                    '' AS indicator_code,
                    '当量值' AS data_caliber,
                    A.category,
                    SUM ( A.indicator_value * b.indicator_value ) AS indicator_value,
                    B.unit,
                    'energy_k103_2,k216_2_enterprise_product_energy_standard' AS data_source
               FROM energy_data_model.k216_2_enterprise_product_energy_standard AS B
          LEFT JOIN energy_data_model.energy_k103_2 AS A 
                 ON A.area_name = B.area_name AND A.area_code = B.area_code AND A.enterprise_code = B.enterprise_code AND A.product_code = B.product_code AND A.indicator_name = '产量' AND A.data_caliber = '实物量' AND A.date_type = 3 AND A.category = '1-本期值'
              WHERE B.indicator_name IN ( '单位产品综合能耗', 
                                         '供电煤耗', 
                                         '综合供热煤耗' ) AND A.product_code IS NOT NULL AND A.start_time >= '2020-01-01' AND A.end_time <= '2023-12-31' AND B.area_code != '150600000000' AND SUBSTRING ( A.area_code, 5, 12 ) > 0
           GROUP BY A.area_code,
                    A.product_code,
                    A.date_type,
                    A.start_time,
                    A.end_time,
                    A.indicator_name,
                    A.data_caliber,
                    A.category,
                    B.efficiency_name ) AS A 
LEFT JOIN ( SELECT
                    /*+GROUP_OPT_FLAG(1)*/
                    A.area_name,
                    A.area_code,
                    A.date_type,
                    A.start_time,
                    A.end_time,
                    A.product_name,
                    A.product_code,
                    B.efficiency_name,
                    A.indicator_name,
                    A.indicator_code,
                    A.data_caliber,
                    A.category,
                    SUM ( A.indicator_value ) AS indicator_value,
                    B.unit,
                    A.data_source
               FROM energy_data_model.k216_2_enterprise_product_energy_standard AS B
          LEFT JOIN energy_data_model.energy_k103_2 AS A 
                 ON A.area_code = B.area_code AND A.enterprise_code = B.enterprise_code AND A.product_code = B.product_code AND A.indicator_name = '产量' AND A.data_caliber = '实物量' AND A.date_type = 3 AND A.category = '1-本期值'
              WHERE B.indicator_name IN ( '单位产品综合能耗', 
                                         '供电煤耗', 
                                         '综合供热煤耗' ) AND A.product_code IS NOT NULL AND A.start_time >= '2020-01-01' AND A.end_time <= '2023-12-31' AND SUBSTRING ( A.area_code, 5, 12 ) > 0
           GROUP BY A.area_code,
                    A.product_code,
                    A.date_type,
                    A.start_time,
                    A.end_time,
                    A.indicator_name,
                    A.data_caliber,
                    A.category,
                    B.efficiency_name ) AS C 
       ON A.area_code = C.area_code AND A.product_code = C.product_code AND A.efficiency_name = C.efficiency_name AND A.date_type = C.date_type AND A.start_time = C.start_time AND A.end_time = C.end_time AND C.indicator_name = '产量' AND C.data_caliber = '实物量' AND C.date_type = 3 AND C.category = '1-本期值'
怎么优化一下执行速度
急急急 两个子查询连接的语句查询效率非常慢

急急急两个子查询连接的语句查询效率非常慢
