3,491
社区成员
发帖
与我相关
我的任务
分享还是sql优化
原版sql，查询时间31秒
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) --去除黑名单成员
...全文
请发表友善的回复…
发表回复
sxq129601 2018-04-04
- 打赏
- 举报
你先拆开来分段执行，看看哪里变慢了。。。
小灰狼W 2018-04-03
- 打赏
- 举报
只看代码只是表面上的，并不准确
最后的1.3秒，是因为在pl/sql dev中，只显示了第一页吧
建议从相关表和字段的统计信息出发，结合执行计划，弄清楚主要的过滤条件在哪。如果都是大表，又没有明确的过滤条件，基本就是全表扫描，只剩代码写得好看不好看的差别了
学海无涯-回头是岸 2018-03-28
- 打赏
- 举报
去掉已开门禁的，就拿出来放在最外面最后去掉 用个公共表达式 with的写法这样，只扫一次数据。
union all不去除重复会比union快。
-一个大坑 2018-03-28
- 打赏
- 举报
g表里的语句
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 --查找人员组在权限表已存在的权限之外需要开通的权限.
union all
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 l.passtype = '1'
and d.cardid is not null
and d.tdate is null --查找员工可通行部门在权限表已存在的权限之外并且门禁点通行类别为‘1.所有员工’需要开通的权限.
union all
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 r.passtype = '0'
and q.empl_category in ('A20', 'B20', 'C20', 'B30')
and q.tdate is null --查找员工可通行部门在权限表已存在的权限之外并且门禁点通行类别为‘0.IDL’需要开通的权限.
union all
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 q.descrshort in
(select t.jobtitle from sec_bd_jobtitle t)
and r.passtype = '2'
and q.tdate is null --查找员工可通行部门在权限表已存在的权限之外并且门禁点通行类别为‘2.主管’需要开通的权限.
union all
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
--查找员工宿舍在权限表已存在的权限之外需要开通的权限
-一个大坑 2018-03-28
- 打赏
- 举报
改版2
把去掉已开门禁和黑名单和left join连接的都写里面来了，最后用union并在一起
这个查询1.3s，和前面的比相当快，但是count计算总数27s，就又慢了很多
这个究竟是快还是慢
select f.doorcode,
f.devno,
f.doorno,
f.emplid,
f.cardid,
f.fromhr,
a1.cnt_template_id agreement1,
b1.cnt_template_id agreement2,
c1.cnt_template_id agreement3
from (select g.doorcode,
g.devno,
g.doorno,
g.emplid,
g.cardid,
g.fromhr
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 --查找人员组在权限表已存在的权限之外需要开通的权限.
) g
where not exists (select 0
from sec_bd_managelist j
where g.emplid = j.emplid) --去除黑名单成员
and not exists (select 0
from sec_authority o
where o.doorcode = g.doorcode
and o.authstatus in (1, 2)
and o.emplid = g.emplid
and o.cardid = g.cardid) --去除已開門禁權限
) f
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 f.emplid = a1.emplid --查找员工所签协议
left join sec_bd_doorinfo b1
on f.doorcode = b1.doorcode --查找门禁所需协议
left join sec_bd_user c1
on f.emplid = c1.emplid --查找非员工所签协议
union
select f.doorcode,
f.devno,
f.doorno,
f.emplid,
f.cardid,
f.fromhr,
a1.cnt_template_id agreement1,
b1.cnt_template_id agreement2,
c1.cnt_template_id agreement3
from (select g.doorcode,
g.devno,
g.doorno,
g.emplid,
g.cardid,
g.fromhr
from (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 l.passtype = '1'
and d.cardid is not null
and d.tdate is null --查找员工可通行部门在权限表已存在的权限之外并且门禁点通行类别为‘1.所有员工’需要开通的权限.
) g
where not exists (select 0
from sec_bd_managelist j
where g.emplid = j.emplid) --去除黑名单成员
and not exists (select 0
from sec_authority o
where o.doorcode = g.doorcode
and o.authstatus in (1, 2)
and o.emplid = g.emplid
and o.cardid = g.cardid) --去除已開門禁權限
) f
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 f.emplid = a1.emplid --查找员工所签协议
left join sec_bd_doorinfo b1
on f.doorcode = b1.doorcode --查找门禁所需协议
left join sec_bd_user c1
on f.emplid = c1.emplid --查找非员工所签协议
union
...后面的类似
-一个大坑 2018-03-28
- 打赏
- 举报
字符长度超了分开发
改版1
用union all代替union，distinct去重。看到每个union都有去掉已开门禁的，就拿出来放在最外面最后去掉。
查询时间15秒，count计算总数15秒
select f.doorcode,
f.devno,
f.doorno,
f.emplid,
f.cardid,
f.fromhr,
a1.cnt_template_id agreement1,
b1.cnt_template_id agreement2,
c1.cnt_template_id agreement3
from (select distinct g.doorcode,
g.devno,
g.doorno,
g.emplid,
g.cardid,
g.fromhr
from (
g表内容在下面
) g
where not exists (select 0
from sec_authority o
where o.doorcode = g.doorcode
and o.authstatus in (1, 2)
and o.emplid = g.emplid
and o.cardid = g.cardid) --去除已開門禁權限
and not exists (select 0
from sec_bd_managelist j
where g.emplid = j.emplid) --去除黑名单成员
) f
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 f.emplid = a1.emplid --查找员工所签协议
left join sec_bd_doorinfo b1
on f.doorcode = b1.doorcode --查找门禁所需协议
left join sec_bd_user c1
on f.emplid = c1.emplid --查找非员工所签协议
