如下SQL,pt_aa_em4_41_t3万 多表记录;pt_aa_em4_41,30万记录,pt_aa_em4_4表,5万多记录,执行以下SQL,花了两个多小时,
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
))
|
