语句优化 实在是太慢了

[netboygg]

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多秒 上千上万条数据就... 高手帮我看看 谢谢了

[hebo2005]

这种明显可以一个update的语句的，就别弄得这么复杂了，肯定会慢的

[netboygg]

不明白 能说清楚点吗? 谢谢 不会写到一句来

[hebo2005]

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)

[kaoziji]

lz的“If v_Row >= 1 Then ”就没有"返回多个行"的错误？
如果update的逻辑很复杂，就考虑trace一下运行的session，tkprof后，获取procedure的执行计划。
消灭其中的所有full table scan等慢的动作。

[cnworm]

把每个语句的执行计划贴出来

[netboygg]

这样写不行吧 x.superid 是从另一张表来的 如果加上那表就会报单行子查询返回多个行

象这样
有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