初学数据库，帮看看下面sql为什么这么慢！！！！！

Question

update lsokzk_tbl lso set lso.LSZ_TEHAI_ZANSUU = (select tehai.tehaizan from
(select sum(a.tehaizan) as tehaizan,  b.LSZ_LSKCD, b.LSZ_LSOKCD, b.LSZ_HINCD, b.LSZ_KAKU 
from
(select HAC_CHUUMON_NO,
HAC_HINCD,
HAC_KAKU,
HAC_LSKCD,
HAC_LSOKCD,
(HAC_HATSU-HAC_NOUNSUU+HAC_UCHIKAN_SU) as tehaizan
from hacchu_tbl
where HAC_HATSU-HAC_NOUNSUU+HAC_UCHIKAN_SU >0
union 
select HAC_CHUUMON_NO,
HAC_HINCD,
HAC_KAKU,
HAC_LSKCD,
HAC_LSOKCD,
case when
(HAC_HATSU-HAC_NOUNSUU+HAC_UCHIKAN_SU)<=0 then 0 end
from hacchu_tbl
where HAC_HATSU-HAC_NOUNSUU+HAC_UCHIKAN_SU <=0) a, lsokzk_tbl b
where a.HAC_HINCD = b.LSZ_HINCD
and a.HAC_KAKU = b.LSZ_KAKU
and a.HAC_LSKCD = b.LSZ_LSKCD
and a.HAC_LSOKCD = b.LSZ_LSOKCD
group by b.LSZ_LSKCD, b.LSZ_LSOKCD, b.LSZ_HINCD, b.LSZ_KAKU 
) tehai
where 
lso.LSZ_HINCD = tehai.LSZ_HINCD
and lso.LSZ_LSKCD = tehai.LSZ_LSKCD
and lso.LSZ_LSOKCD = tehai.LSZ_LSOKCD
and lso.LSZ_KAKU = tehai.LSZ_KAKU)
where exists (select 1 from
(select sum(a.tehaizan) as tehaizan,  b.LSZ_LSKCD, b.LSZ_LSOKCD, b.LSZ_HINCD, b.LSZ_KAKU 
from
(select HAC_CHUUMON_NO,
HAC_HINCD,
HAC_KAKU,
HAC_LSKCD,
HAC_LSOKCD,
(HAC_HATSU-HAC_NOUNSUU+HAC_UCHIKAN_SU) as tehaizan
from hacchu_tbl
where HAC_HATSU-HAC_NOUNSUU+HAC_UCHIKAN_SU >0
union 
select HAC_CHUUMON_NO,
HAC_HINCD,
HAC_KAKU,
HAC_LSKCD,
HAC_LSOKCD,
case when
(HAC_HATSU-HAC_NOUNSUU+HAC_UCHIKAN_SU)<=0 then 0 end
from hacchu_tbl
where HAC_HATSU-HAC_NOUNSUU+HAC_UCHIKAN_SU <=0) a, lsokzk_tbl b
where a.HAC_HINCD = b.LSZ_HINCD
and a.HAC_KAKU = b.LSZ_KAKU
and a.HAC_LSKCD = b.LSZ_LSKCD
and a.HAC_LSOKCD = b.LSZ_LSOKCD
group by b.LSZ_LSKCD, b.LSZ_LSOKCD, b.LSZ_HINCD, b.LSZ_KAKU 
) tehai
where 
lso.LSZ_HINCD = tehai.LSZ_HINCD
and lso.LSZ_LSKCD = tehai.LSZ_LSKCD
and lso.LSZ_LSOKCD = tehai.LSZ_LSOKCD
and lso.LSZ_KAKU = tehai.LSZ_KAKU);

Answer 1

可以参考一下这个帖子，虽然有点长，但很值得好好看看。http://topic.csdn.net/u/20080718/17/dc1d21b4-47f7-41fc-9e32-a0f25358ad5c.html?seed=1866295945

Answer 2

顺便帮你整理了一下sql，让大家看起来方便一点。
/* Formatted on 2008/07/23 13:29 (Formatter Plus v4.8.7) */
UPDATE lsokzk_tbl lso
   SET lso.lsz_tehai_zansuu =
          (SELECT tehai.tehaizan
             FROM (SELECT   SUM (a.tehaizan) AS tehaizan, b.lsz_lskcd,
                            b.lsz_lsokcd, b.lsz_hincd, b.lsz_kaku
                       FROM (SELECT hac_chuumon_no, hac_hincd, hac_kaku,
                                    hac_lskcd, hac_lsokcd,
                                    (hac_hatsu - hac_nounsuu + hac_uchikan_su
                                    ) AS tehaizan
                               FROM hacchu_tbl
                              WHERE hac_hatsu - hac_nounsuu + hac_uchikan_su >
                                                                             0
                             UNION
                             SELECT hac_chuumon_no, hac_hincd, hac_kaku,
                                    hac_lskcd, hac_lsokcd,
                                    CASE
                                       WHEN (  hac_hatsu
                                             - hac_nounsuu
                                             + hac_uchikan_su
                                            ) <= 0
                                          THEN 0
                                    END
                               FROM hacchu_tbl
                              WHERE hac_hatsu - hac_nounsuu + hac_uchikan_su <=
                                                                             0) a,
                            lsokzk_tbl b
                      WHERE a.hac_hincd = b.lsz_hincd
                        AND a.hac_kaku = b.lsz_kaku
                        AND a.hac_lskcd = b.lsz_lskcd
                        AND a.hac_lsokcd = b.lsz_lsokcd
                   GROUP BY b.lsz_lskcd, b.lsz_lsokcd, b.lsz_hincd,
                            b.lsz_kaku) tehai
            WHERE lso.lsz_hincd = tehai.lsz_hincd
              AND lso.lsz_lskcd = tehai.lsz_lskcd
              AND lso.lsz_lsokcd = tehai.lsz_lsokcd
              AND lso.lsz_kaku = tehai.lsz_kaku)
 WHERE EXISTS (
          SELECT 1
            FROM (SELECT   SUM (a.tehaizan) AS tehaizan, b.lsz_lskcd,
                           b.lsz_lsokcd, b.lsz_hincd, b.lsz_kaku
                      FROM (SELECT hac_chuumon_no, hac_hincd, hac_kaku,
                                   hac_lskcd, hac_lsokcd,
                                   (hac_hatsu - hac_nounsuu + hac_uchikan_su
                                   ) AS tehaizan
                              FROM hacchu_tbl
                             WHERE hac_hatsu - hac_nounsuu + hac_uchikan_su >
                                                                             0
                            UNION
                            SELECT hac_chuumon_no, hac_hincd, hac_kaku,
                                   hac_lskcd, hac_lsokcd,
                                   CASE
                                      WHEN (  hac_hatsu
                                            - hac_nounsuu
                                            + hac_uchikan_su
                                           ) <= 0
                                         THEN 0
                                   END
                              FROM hacchu_tbl
                             WHERE hac_hatsu - hac_nounsuu + hac_uchikan_su <=
                                                                             0) a,
                           lsokzk_tbl b
                     WHERE a.hac_hincd = b.lsz_hincd
                       AND a.hac_kaku = b.lsz_kaku
                       AND a.hac_lskcd = b.lsz_lskcd
                       AND a.hac_lsokcd = b.lsz_lsokcd
                  GROUP BY b.lsz_lskcd, b.lsz_lsokcd, b.lsz_hincd, b.lsz_kaku) tehai
           WHERE lso.lsz_hincd = tehai.lsz_hincd
             AND lso.lsz_lskcd = tehai.lsz_lskcd
             AND lso.lsz_lsokcd = tehai.lsz_lsokcd
             AND lso.lsz_kaku = tehai.lsz_kaku);

Answer 3

那就是你的SQL的链接有问题
先select看一下数据!

Answer 4

你可以看下你的执行计划
set autotrace on

Answer 5

你的语句前加EXPLAIN PLAN FOR
看一下执行计划，然后贴上来

Answer 6

语句前加EXPLAIN PLAN FOR 执行，然后再执行select * from table(dbms_xplan.display);
就能看到语句的执行计划

Answer 7

我在语句前加了EXPLAIN PLAN FOR ，提示PLAN表没有找到，，晕

Answer 8

select正常，出现3758个数据， 

Answer 9

是要在整个update的sql语句前加EXPLAIN PLAN FOR 的吗？