高手请进，请教Oracle Update问题~

Question

我们平时大部分都用MS SQL
Oracle平台的语句都是SQL强行转过来的下面这个语句特别郁闷：
UPDATE OFSS0410.tOF_JJFE A SET FHFS=
(SELECT  B.FHFS FROM (SELECT KHH,JJZH,TADM,JJDM,FHFS FROM OFSS0410.tOF_JJWTQR WHERE WTH IN(SELECT MAX(WTH) FROM OFSS0410.tOF_JJWTQR where SBJG=111 AND YWDM='129' GROUP BY KHH,JJZH,TADM,JJDM)) B
where A.KHH=B.KHH AND A.JJZH=B.JJZH AND A.TADM=B.TADM AND A.JJDM=B.JJDM)
WHERE ROWID IN (SELECT A.ROWID FROM (SELECT KHH,JJZH,TADM,JJDM,FHFS FROM OFSS0410.tOF_JJWTQR WHERE WTH IN(SELECT MAX(WTH) FROM OFSS0410.tOF_JJWTQR where SBJG=111 AND YWDM='129' GROUP BY KHH,JJZH,TADM,JJDM)) B
 where A.KHH=B.KHH AND A.JJZH=B.JJZH AND A.TADM=B.TADM AND A.JJDM=B.JJDM)其中的子查询(SELECT KHH,JJZH,TADM,JJDM,FHFS FROM OFSS0410.tOF_JJWTQR WHERE WTH IN(SELECT MAX(WTH) FROM OFSS0410.tOF_JJWTQR where SBJG=111 AND YWDM='129' GROUP BY KHH,JJZH,TADM,JJDM)) 很快就出来了
整个语句——在客户那边半个小时出不来，把数据还原到我们的测试环境，能出结果，但是也要花30秒左右请教高手：
1、在客户那边半个小时出不来，大概什么原因？
2、这种语句，有什么更好的写法不？
3、把子查询的结果用游标逐条修改OFSS0410.tOF_JJFE 却很快，这种语句，Oracle下是不是游标的效率更高？

Answer 1

Update ..
WHERE ROWID IN ()是我们一贯的写法是不是要改了？

Answer 2

hongqi162(失踪的月亮) ( ) 信誉：100    Blog   加为好友  2007-04-12 11:00:25  得分: 0  
 
 
   看你的sql就知道为什么慢了
  ------------------------------------------------
呵呵
那你给个改进的建议阿

Answer 3

CONSTRAINT PK_OFJJFE PRIMARY KEY (KHH,TADM,JJZH,JJDM,SFFS)主键索引，重建了在我们的测试环境还是要31秒

Answer 4

UPDATE OFSS0410.tOF_JJFE A
   SET FHFS = (SELECT B.FHFS
                 FROM (SELECT KHH, JJZH, TADM, JJDM, FHFS
                         FROM OFSS0410.tOF_JJWTQR
                        WHERE WTH IN (SELECT MAX(WTH)
                                        FROM OFSS0410.tOF_JJWTQR
                                       where SBJG = 111
                                         AND YWDM = '129'
                                       GROUP BY KHH, JJZH, TADM, JJDM)) B
                where A.KHH = B.KHH
                  AND A.JJZH = B.JJZH
                  AND A.TADM = B.TADM
                  AND A.JJDM = B.JJDM)
 WHERE ROWID IN
       (SELECT A.ROWID
          FROM (SELECT KHH, JJZH, TADM, JJDM, FHFS
                  FROM OFSS0410.tOF_JJWTQR
                 WHERE WTH IN (SELECT MAX(WTH)
                                 FROM OFSS0410.tOF_JJWTQR
                                where SBJG = 111
                                  AND YWDM = '129'
                                GROUP BY KHH, JJZH, TADM, JJDM)) B
         where A.KHH = B.KHH
           AND A.JJZH = B.JJZH
           AND A.TADM = B.TADM
           AND A.JJDM = B.JJDM)
其中：WHERE ROWID IN
       (SELECT A.ROWID
不应该是A. 而是B. 吧？

Answer 5

是A.ROWID没错~
非常感谢楼上帮我把语句格式化出来~

Answer 6

UPDATE OFSS0410.tOF_JJFE A
   SET A.FHFS = (SELECT B.FHFS
                 FROM (SELECT KHH, JJZH, TADM, JJDM,FHFS
                        FROM OFSS0410.tOF_JJWTQR C
                        WHERE NOT exists(
                                      SELECT WTH
                                       FROM OFSS0410.tOF_JJWTQR D
                                       where KHH=C.KHH
                                         AND D.JJZH=C.JZH
                                         AND D.TADM=C.TADM
                                         AND D.JJDM=C.JJDM
                                         AND D.SBJG = 111
                                         AND D.YWDM = '129'
                                         AND D.WTH>C.WTH
                                      )
                       ) B
                where A.KHH = B.KHH
                  AND A.JJZH = B.JJZH
                  AND A.TADM = B.TADM
                  AND A.JJDM = B.JJDM)
 WHERE exists
       (SELECT 1
          FROM (SELECT KHH, JJZH, TADM, JJDM, FHFS
                  FROM OFSS0410.tOF_JJWTQR F
                  WHERE NOT exists(
                                  SELECT WTH
                                   FROM OFSS0410.tOF_JJWTQR E
                                   where KHH=C.KHH
                                     AND E.JJZH=F.JZH
                                     AND E.TADM=F.TADM
                                     AND E.JJDM=F.JJDM
                                     AND E.SBJG = 111
                                     AND E.YWDM = '129'
                                     AND E.WTH>F.WTH
                                   )
               ) B
         where A.KHH = B.KHH
           AND A.JJZH = B.JJZH
           AND A.TADM = B.TADM
           AND A.JJDM = B.JJDM)
没法试,你自己执行看看
in的方式最好用EXISTS代替,效率差很多