求助 ORACLE 执行UPDATE 很慢
modev 2009-05-19 05:08:18 如下 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 remark =
'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
))