--部门
UPDATE ORG_DEPARTMENT t
SET PEOPLENUMBER = (SELECT COUNT(*) FROM EMP_EMPLOYEE e,EMP_POSITIONCHANGE p
WHERE e.department = t.id
and p.department = t.id
and p.employee = e.id
and e.state in(1,11) 状态1表示正式员工11表示试用期员工
and p.isprimary = 1 此员工是否有兼职
and p.isvalid = 1 此员工是否有效
and e.validfrom<sysdate
and e.validto>sysdate
and p.changedate<sysdate
and p.validfrom<sysdate
and p.validto>sysdate
and p.department is not null)上面是一条统计部门人数的sql~~主要是统计每个部门的正式员工和试用期员工和不是兼职的员工~~~~
现在出现如下情况
1当员工表只有几十或几百个人的时候这条sql执行的速度还可以~如果员工表有
上万条数据的时候这条sql就执行不下去了
请问如何优化这条sql语句呢
UPDATE ORG_DEPARTMENT t
SET PEOPLENUMBER = (SELECT COUNT(*) FROM EMP_EMPLOYEE e,EMP_POSITIONCHANGE p
WHERE e.department = t.id
and p.department = t.id
and p.employee = e.id
and e.state in(1,11) 状态1表示正式员工11表示试用期员工
and p.isprimary = 1 此员工是否有兼职
and p.isvalid = 1 此员工是否有效
and e.validfrom<sysdate
and e.validto>sysdate
and p.changedate<sysdate
and p.validfrom<sysdate
and p.validto>sysdate
and p.department is not null)上面是一条统计部门人数的sql~~主要是统计每个部门的正式员工和试用期员工和不是兼职的员工~~~~
现在出现如下情况
1当员工表只有几十或几百个人的时候这条sql执行的速度还可以~如果员工表有
上万条数据的时候这条sql就执行不下去了
请问如何优化这条sql语句呢
SET PEOPLENUMBER = (SELECT COUNT(*) FROM EMP_EMPLOYEE e,EMP_POSITIONCHANGE p
WHERE e.department = t.id
and p.department = t.id
and p.employee = e.id
and e.state in(1,11) 状态1表示正式员工11表示试用期员工
and p.isprimary = 1 此员工是否有兼职
and p.isvalid = 1 此员工是否有效
and e.validfrom<sysdate
and e.validto>sysdate
and p.changedate<sysdate
and p.validfrom<sysdate
and p.validto>sysdate
and p.department is not null)以上SQL给几点建议
1:and e.state in(1,11) --〉更换为exist,in的全表扫描很影响效率
2:所有的< 和 > 请更换为 between and
3:执行anylize,对数据量大的关联字段 create index
--------------------------------------------------------------------------------
开了IE就可以玩的。http://pet.mop.com/?u=8094002来了找精灵之森(网通)的小麦兜儿,就是我!用Mop的ID就能玩的
--------------------------------------------------------------------------------
select count(*) from
(select * from EMP_EMPLOYEE where state in(1,11) and validfrom<sysdate and validto>sysdate) e,
(select * from EMP_POSITIONCHANGE where isprimary = 1 and isvalid = 1 and changedate<sysdate
and validfrom<sysdate and validto>sysdate and department is not null) p
WHERE e.department = t.id
and p.department = t.id
and p.employee = e.id
能去掉最多纪录的最接近where条件,建立索引