ORACLE跟新数据的性能提升（通过中间表）

--更新历程权力值
--1.查询效率慢的
update FW_WF_COURSE c set c.power =
(select distinct from jbpm_taskinstance t1 where t1.id_ = c.task_id)
where c.power is null;

 

--查询效率快
create table FW_WF_COU_BP as
select * from (select distinct c.course_id ,t1.power_ from FW_WF_COURSE c ,jbpm_taskinstance t1
where t1.id_ = c.task_id and c.power is null and nvl(t1.power_ ,0) > 0  );

alter table FW_WF_COU_BP modify course_id unique;

 

update (select c.power cp ,cb.power tp from FW_WF_COURSE c ,FW_WF_COU_BP cb where c.course_id = cb.course_id)
set cp = tp;
commit;

 

--跟新历程节点编码
--1.查询效率慢的
update FW_WF_COURSE c set c.node_code =
(select n.code_ from jbpm_node n where n.id = (select nvl(k.tasknode_ ,k.startstate_) from jbpm_task k
where k.id = (select distinct t1.task_ from jbpm_taskinstance t1 where t1.id_ = c.task_id)));
where c.power is null;

 

--查询效率快
create table FW_WF_COU_BP as
select * from (
       select distinct c.course_id ,n.code_ from FW_WF_COURSE c ,jbpm_node n ,jbpm_taskinstance t1 ,jbpm_task tk
       where c.task_id = t1.id_ and t1.task_ = tk.id_ and nvl(k.tasknode_ ,k.startstate_) = n.id_
       and c.node_code is null and n.code_ is not null

);

 

alter table FW_WF_COU_BP modify course_id unique;

 

update (select c.node_code cn ,cb.code cc from FW_WF_COURSE c ,FW_WF_COU_BP cb where c.course_id = cb.course_id)
set cn = cc;
commit;

 

truncate table FW_WF_COU_BP;

 

[e-mail]