请大神帮忙优化此oracle的sql

发布网友发布时间：2022-12-16 15:55

共1个回答

热心网友时间：2024-10-21 09:18

最好不要用not exists。

而且我对你们表的搭建表示怀疑，一些字段可以牺牲下存储空间，使信息更充足，例如oa_approve表是否可以含有oa_affix_seal的一些信息，查询时就不用关联这2个表了。

而且你这个sql涉及6个表,不知道每个表的结构(是否有索引,数据行数),所以单纯从sql语法上,建议用left join。下面是我假设的一段sql,你要注意的是结构而不是调试了就直接用：

select a.approve_id    as approveId,
       a.approve_title as approveTitle,
       a.created_date  as createdDateString,
       a.updated_date  as updatedDateString
  from oa_approve a
  left join oa_affix_seal t1
    on a.approve_id = t.approve_id
   and t.is_valid <> 'Y'
  left join oa_approve_param t2
    on t2.approve_id = a.approve_id
   and t.param_key = 'sealType'
   and t2.param_value = 'E'
   and t2.is_valid = 'Y'
  left join oa_approve_task ta
    on ta.approve_id = a.approve_id
   and ta.is_valid = 'Y'
   and ta.task_type_code = '1'
   and ta.task_status_code = 'A'
   AND ta.templ_id = n1.templ_id
  left join oa_flow_templ_node n1
    on ta.templ_node_code = n1.templ_node_code
   and n1.is_valid = 'Y'
where a.is_valid = 'Y'
      /*and not exists (select 1
       from oa_affix_seal t1
      where t.approve_id = a.approve_id
        and t.is_valid = 'Y')*/
   and t1.approve_id is not null
      /* and exists
      (select 1
               from oa_approve_param t2
              where t.approve_id = a.approve_id
                and t.param_key = 'sealType'
                and t.param_value = 'E'
                and t.is_valid = 'Y')*/
   and t2.approve_id is not null
      /*and ((a.approve_status_code = 'E' and a.approve_result = 'Y') or exists
      (select 1
         from oa_approve_task ta, oa_flow_templ_node n1
        where ta.is_valid = 'Y'
          and ta.task_type_code = '1'
          anD ta.task_status_code = 'A'
          AND ta.templ_id = n1.templ_id
          and ta.templ_node_code = n1.templ_node_code
          and n1.is_valid = 'Y'
          and ta.approve_id = a.approve_id
          and n1.templ_node_step >
              (select n2.templ_node_step
                 from oa_flow_templ_param p, oa_flow_templ_node n2
                where p.is_valid = 'Y'
                  and n2.is_valid = 'Y'
                  and p.param_key = 'SEND_IESS_CONTROLLER'
                  and p.param_value = n2.templ_node_code
                  and n2.templ_id = ta.templ_id
                  and n2.templ_ver = ta.templ_ver
                  and rownum = 1)))*/
   and ((a.approve_status_code = 'E' and a.approve_result = 'Y') or
       (ta.approve_id is not null and n1.templ_node_code is not null and
       n1.templ_node_step >
       (select n2.templ_node_step
            from oa_flow_templ_param p, oa_flow_templ_node n2
           where p.is_valid = 'Y'
             and n2.is_valid = 'Y'
             and p.param_key = 'SEND_IESS_CONTROLLER'
             and p.param_value = n2.templ_node_code
             and n2.templ_id = ta.templ_id
             and n2.templ_ver = ta.templ_ver
             and rownum = 1)))
order by a.updated_date desc;