求助 ORACLE 执行UPDATE 很慢

Question

如下 UPDATE SQL pt_aa_em4_41_t 表 : 3万多数据;
pt_aa_em4_41 表 29万数据;
pt_aa_em4_4 表:6万数据;执行以下SQL超慢,花了近3个小时UPDATE ims.pt_aa_em4_41_t
   SET re =
               '15更新子表于:  ' || TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24:mi:ss'),
       (pt_aa_em4_41_t.spool_date, pt_aa_em4_41_t.tfd_date,
        pt_aa_em4_41_t.emr_date) =
          (SELECT DECODE (pt_aa_em4_41.spool_date,
                          NULL, pt_aa_em4_41_t.spool_date,
                          NULL
                         ),
                  DECODE (pt_aa_em4_41.tfd_date,
                          NULL, pt_aa_em4_41_t.tfd_date,
                          NULL
                         ),
                  DECODE (pt_aa_em4_41.emr_date,
                          NULL, pt_aa_em4_41_t.emr_date,
                          NULL
                         )
             FROM (SELECT   pt_aa_em4_41.isometric, pt_aa_em4_41.spool,
                            MAX (pt_aa_em4_41.spool_date) AS spool_date,
                            MAX (pt_aa_em4_41.tfd_date) AS tfd_date,
                            MAX (pt_aa_em4_41.emr_date) AS emr_date
                       FROM (SELECT pt_aa_em4_41.*
                               FROM ims.pt_aa_em4_41, ims.pt_aa_em4_4
                              WHERE pt_aa_em4_41.parent_uuid =
                                                              pt_aa_em4_4.uuid
                                AND (   pt_aa_em4_4.TYPE = '1'
                                     OR pt_aa_em4_4.TYPE = '4'
                                    )) pt_aa_em4_41
                   GROUP BY (pt_aa_em4_41.isometric, pt_aa_em4_41.spool)) pt_aa_em4_41
            WHERE (    pt_aa_em4_41_t.isometric = pt_aa_em4_41.isometric
                   AND pt_aa_em4_41_t.spool = pt_aa_em4_41.spool
                  ))
 WHERE EXISTS (
          SELECT 1
            FROM (SELECT   pt_aa_em4_41.isometric, pt_aa_em4_41.spool,
                           MAX (pt_aa_em4_41.spool_date) AS spool_date,
                           MAX (pt_aa_em4_41.tfd_date) AS tfd_date,
                           MAX (pt_aa_em4_41.emr_date) AS emr_date
                      FROM (SELECT pt_aa_em4_41.*
                              FROM ims.pt_aa_em4_41, ims.pt_aa_em4_4
                             WHERE pt_aa_em4_41.parent_uuid = pt_aa_em4_4.uuid
                               AND (   pt_aa_em4_4.TYPE = '1'
                                    OR pt_aa_em4_4.TYPE = '4'
                                   )) pt_aa_em4_41
                  GROUP BY (pt_aa_em4_41.isometric, pt_aa_em4_41.spool)) pt_aa_em4_41
           WHERE (    pt_aa_em4_41_t.isometric = pt_aa_em4_41.isometric
                  AND pt_aa_em4_41_t.spool = pt_aa_em4_41.spool
                 ))

Answer 1

楼主这个语句太复杂了，主要是你的where子句的问题。如果可以的话，建议分成若干小的sql语句。

Answer 2

谢谢楼上的回复,这个SQL是在存储过程提取出来的,就是说在存储过程中,已经分成若干个小的SQL语句了.

Answer 3

如果update的字段比较多，不要用update,oracle里面的update语句很垃圾的建议换成merge试一试

Answer 4

就只更新几个字段,应该不算多吧?不知类似以上的SQL是否还有优化空间?或有否替代方法??

Answer 5

SELECT pt_aa_em4_41.* 
--
建议这个改为选取个别字段，如果需要全部字段，也要全部写出来

Answer 6

问题已基本解决:
在表pt_aa_em4_41创建两索引,索引列分别:isometric和spool.