这个sql怎么优化

[-一个大坑]

查询要30多秒，优化一般怎么优化？

select distinct g.doorcode,
                g.devno,
                g.doorno,
                g.emplid,
                g.cardid,
                g.fromhr,
                a1.cnt_template_id agreement1,
                b1.cnt_template_id agreement2,
                c1.cnt_template_id agreement3
  from (select b.doorcode, k.devno, k.doorno, a.emplid, a.cardid, a.fromhr
          from v_sec_user a, sec_bd_usergroup b, sec_bd_doorinfo k
         where a.usergroupname = b.groupname
           and b.doorcode = k.doorcode
           and not exists (select *
                  from sec_authority c
                 where c.emplid = a.emplid
                   and c.cardid = a.cardid
                   and c.doorcode = b.doorcode
                   and c.authstatus in (1, 2)
                   and c.rno is null) --查找人员组在权限表已存在的权限之外需要开通的权限.
        union
        select e.doorcode,
               l.devno,
               l.doorno,
               d.emplid,
               d.cardid,
               'Y' as fromhr
          from sec_bd_door_psdept e, employee d, sec_bd_doorinfo l
         where e.deptid = d.deptid
           and e.doorcode = l.doorcode
           and not exists (select *
                  from sec_authority f
                 where f.doorcode = e.doorcode
                   and f.emplid = d.emplid
                   and f.cardid = d.cardid
                   and f.authstatus in (1, 2)
                   and f.rno is null)
           and l.passtype = '1'
           and d.cardid is not null
           and d.tdate is null --查找员工可通行部门在权限表已存在的权限之外并且门禁点通行类别为‘1.所有员工’需要开通的权限.
        union
        select p.doorcode,
               r.devno,
               r.doorno,
               q.emplid,
               q.cardid,
               'Y' as fromhr
          from sec_bd_door_psdept p, employee q, sec_bd_doorinfo r
         where p.deptid = q.deptid
           and p.doorcode = r.doorcode
           and not exists
         (select *
                  from sec_authority s
                 where s.doorcode = p.doorcode
                   and s.emplid = q.emplid
                   and s.cardid = q.cardid
                   and s.authstatus in (1, 2)
                   and s.rno is null)
           and r.passtype = '0'
           and q.empl_category in ('A20', 'B20', 'C20', 'B30')
           and q.tdate is null --查找员工可通行部门在权限表已存在的权限之外并且门禁点通行类别为‘0.IDL’需要开通的权限.
        union
        select p.doorcode,
               r.devno,
               r.doorno,
               q.emplid,
               q.cardid,
               'Y' as fromhr
          from sec_bd_door_psdept p, employee q, sec_bd_doorinfo r
         where p.deptid = q.deptid
           and p.doorcode = r.doorcode
           and not exists
         (select *
                  from sec_authority s
                 where s.doorcode = p.doorcode
                   and s.emplid = q.emplid
                   and s.cardid = q.cardid
                   and s.authstatus in (1, 2)
                   and s.rno is null)
           and q.descrshort in (select t.jobtitle from sec_bd_jobtitle t)
           and r.passtype = '2'
           and q.tdate is null --查找员工可通行部门在权限表已存在的权限之外并且门禁点通行类别为‘2.主管’需要开通的权限.
        union
        select m.doorcode, m.devno, m.doorno, n.emplid, n.cardid, n.fromhr
          from (select t.doorcode,
                       t.devno,
                       t.doorno,
                       regexp_substr(t.building, '[^,]+', 1, level, 'i') building
                  from sec_bd_doorinfo t
                connect by prior rowid = rowid
                       and prior dbms_random.value() is not null
                       and level <= regexp_count(building, ',') + 1) m,
               v_sec_user n
         where m.building = n.dormbuilding
           and not exists (select 0
                  from sec_authority o
                 where m.doorcode = o.doorcode
                   and o.authstatus in (1, 2)
                   and o.emplid = n.emplid
                   and o.cardid = n.cardid) --查找员工宿舍在权限表已存在的权限之外需要开通的权限
        ) g
  left join (select t.emplid,
                    listagg(t.cnt_template_id, ',') within group(order by t.emplid) cnt_template_id
               from sec_ndainfo t
              group by t.emplid) a1
    on g.emplid = a1.emplid  --查找员工所签协议
  left join sec_bd_doorinfo b1
    on g.doorcode = b1.doorcode  --查找门禁所需协议
  left join sec_bd_user c1
    on g.emplid = c1.emplid  --查找非员工所签协议
 where not exists
 (select 0 from sec_bd_managelist j where g.emplid = j.emplid) --去除黑名单成员

[学海无涯-回头是岸]

习惯把长的子查询 ，派生表 这些用cet表

[liu志坚]

这个sql 这么长，要优化的话建议 。把各个部分分开执行下，看哪个耗时，针对耗时的优化。或者直接做个10046事件，耗时的地方就很明显可以看到了，然后针对性优化。

[-一个大坑]

引用 2 楼 liuzhijian2008x 的回复:

这个sql 这么长，要优化的话建议 。把各个部分分开执行下，看哪个耗时，针对耗时的优化。或者直接做个10046事件，耗时的地方就很明显可以看到了，然后针对性优化。

合成g表的里面的语句，每个0.5秒左右。5个sql用union并一下就变成21秒了 再加上g表外面的left join链接的几个表就用30多秒了

[liu志坚]

引用 3 楼 happy4944 的回复:

[quote=引用 2 楼 liuzhijian2008x 的回复:] 这个sql 这么长，要优化的话建议 。把各个部分分开执行下，看哪个耗时，针对耗时的优化。或者直接做个10046事件，耗时的地方就很明显可以看到了，然后针对性优化。

合成g表的里面的语句，每个0.5秒左右。5个sql用union并一下就变成21秒了 再加上g表外面的left join链接的几个表就用30多秒了[/quote] union 是需要去重的，没有必要去重就用union all吧，估计排序去重花掉了很多时间。

[jdsnhan]

要结合语句和业务 比如，用union all 代替union，省去排序的过程 比如，每个子查询都用到了sec_bd_doorinfo表，那么考虑，是否可以把最外层的left join融进去。 但都要从你的实际业务出发

[-一个大坑]

引用 5 楼 jdsnhan 的回复:

要结合语句和业务 比如，用union all 代替union，省去排序的过程 比如，每个子查询都用到了sec_bd_doorinfo表，那么考虑，是否可以把最外层的left join融进去。 但都要从你的实际业务出发

用union all 代替union后，合成G表的语句直接从,21秒变成0.7秒，区别好大。但是和外面的left join一连还是要18秒