Declare
v_Row Number(12) := 0;
Begin
For x In (Select m.Plm_m_Id As Superid
From dqplm.Plm_Psm_Itemmaster_Revision m, --35万条数据
dqplm.plm_cus_tipart i, --35万条数据
dqplm.yt_test y --10万条数据
Where m.plm_r_oid = i.plm_revisionoid
and m.plm_m_lastrevision = m.plm_r_revision
and m.plm_r_lastiteration = i.plm_iteration
--and m.plm_m_id = 'R4600A-712000A002'
and m.plm_m_id = y.superid
and m.Plm_m_Id Like 'X%') Loop
Select Count(*)
Into v_Row
From dqplm.plm_psm_itemmaster_revision m,
dqplm.plm_cus_tipart i,
dqplm.yt_test y
Where m.plm_r_oid = i.plm_revisionoid
and m.plm_m_lastrevision = m.plm_r_revision
and m.plm_r_lastiteration = i.plm_iteration
--and m.plm_m_id = 'R4600A-712000A002'
and y.superid = x.superid
and m.Plm_m_Id Like 'X%';
--Dbms_Output.Put_Line(v_row);
--Dbms_Output.Put_Line(x.superid);
If v_Row >= 1 Then
Update dqplm.Plm_Cus_Tipart n
Set n.plm_materialcode = (Select wt.subid
From Dqplm.Yt_Test Wt
Where Wt.Superid = x.Superid)
Where Exists (Select '1'
From dqplm.Plm_Psm_Itemmaster_Revision m
Where m.Plm_m_Lastrevision = m.Plm_r_Revision
And m.Plm_m_Class = 'TIPART'
and m.Plm_m_Id = x.Superid
and m.Plm_r_Oid = n.Plm_Revisionoid
and m.plm_r_lastiteration = n.plm_iteration
);
Else
Exit;
End If;
End Loop;
End;太慢了 实际几十条的数据更新 要10多秒 上千上万条数据就... 高手帮我看看 谢谢了
v_Row Number(12) := 0;
Begin
For x In (Select m.Plm_m_Id As Superid
From dqplm.Plm_Psm_Itemmaster_Revision m, --35万条数据
dqplm.plm_cus_tipart i, --35万条数据
dqplm.yt_test y --10万条数据
Where m.plm_r_oid = i.plm_revisionoid
and m.plm_m_lastrevision = m.plm_r_revision
and m.plm_r_lastiteration = i.plm_iteration
--and m.plm_m_id = 'R4600A-712000A002'
and m.plm_m_id = y.superid
and m.Plm_m_Id Like 'X%') Loop
Select Count(*)
Into v_Row
From dqplm.plm_psm_itemmaster_revision m,
dqplm.plm_cus_tipart i,
dqplm.yt_test y
Where m.plm_r_oid = i.plm_revisionoid
and m.plm_m_lastrevision = m.plm_r_revision
and m.plm_r_lastiteration = i.plm_iteration
--and m.plm_m_id = 'R4600A-712000A002'
and y.superid = x.superid
and m.Plm_m_Id Like 'X%';
--Dbms_Output.Put_Line(v_row);
--Dbms_Output.Put_Line(x.superid);
If v_Row >= 1 Then
Update dqplm.Plm_Cus_Tipart n
Set n.plm_materialcode = (Select wt.subid
From Dqplm.Yt_Test Wt
Where Wt.Superid = x.Superid)
Where Exists (Select '1'
From dqplm.Plm_Psm_Itemmaster_Revision m
Where m.Plm_m_Lastrevision = m.Plm_r_Revision
And m.Plm_m_Class = 'TIPART'
and m.Plm_m_Id = x.Superid
and m.Plm_r_Oid = n.Plm_Revisionoid
and m.plm_r_lastiteration = n.plm_iteration
);
Else
Exit;
End If;
End Loop;
End;太慢了 实际几十条的数据更新 要10多秒 上千上万条数据就... 高手帮我看看 谢谢了
UPDATE dqplm.plm_cus_tipart n
SET n.plm_materialcode = (SELECT wt.subid
FROM dqplm.yt_test wt
WHERE wt.superid = x.superid)
WHERE EXISTS (
SELECT '1'
FROM dqplm.plm_psm_itemmaster_revision m
WHERE m.plm_m_lastrevision = m.plm_r_revision
AND m.plm_m_class = 'TIPART'
AND m.plm_r_oid = n.plm_revisionoid
AND m.plm_r_lastiteration = n.plm_iteration)
AND EXISTS (
SELECT 1
FROM dqplm.plm_psm_itemmaster_revision m, dqplm.yt_test y
WHERE m.plm_r_oid = n.plm_revisionoid
AND m.plm_m_lastrevision = m.plm_r_revision
AND m.plm_r_lastiteration = n.plm_iteration
--and m.plm_m_id = 'R4600A-712000A002'
AND m.plm_m_id LIKE 'X%'
GROUP BY m.plm_m_id
HAVING COUNT (*) >= 1)
有2个表 根据t1.a=t2.a 来更新t1.b=t2.b: update t1 set b=(select t2.b from t2 where t1.a=t2.a); 在from里添加一个t1写成如下语句就会报单行子查询返回多行
update t1 set b=(select t2.b from t2,t1 where t1.a=t2.a); 添加第3张表也不行:如果要添加第3张表的条件 改怎么写?
update t1 set b=(select t2.b from t2,t3,t1 where t1.a=t2.a and t1.a = t3.a and t2.b=t3.b); 写成这样会编译不通过报ORA-00600内部错误代码,参数:[qctVCO:csform],[0],[0],[0],[0],[1],[1],[0]
update t1 set b=(select t2.b from t2 Where t2.b in
(select t2.b from t2,t3,t1 where t1.a=t2.a and t1.a = t3.a and t2.b=t3.b)) ------------------------------
这里有3张表yt_test,plm_cus_tipart,plm_psm_itemmaster_revision
如果update的逻辑很复杂,就考虑trace一下运行的session,tkprof后,获取procedure的执行计划。
消灭其中的所有full table scan等慢的动作。