如何分解这个相关的 SQL->Oracle 语句？

Question

UPDATE t1
   SET t1.language_id = (SELECT distinct(CASE WHEN NETWORK.nid = 11 
                                  THEN 10
                                  ELSE 7
                              END)
                       FROM PROFILE,
                            NETWORK
                      WHERE PROFILE.STATUS IN ('A','U','S','H','I')
                        AND PROFILE.mid = t1.mid
                        AND NETWORK.mid = t1.mid
                        AND t1.is_deleted = 'N')
   AND EXISTS( SELECT 1
              FROM PROFILE,
                   NETWORK
             WHERE PROFILE.STATUS IN ('A','U','S','H','I')
               AND PROFILE.mid = t1.mid
               AND NETWORK.mid = t1.mid
               AND t1.is_deleted = 'N');

这是一种缓慢的方式。对Use语句使用不同的更新?为10和7做一次更新。

Answer 1

在查询t1表数据库中的每条记录时，分别对NETWORK和PROFILE表执行查询。实际上，您只需要扫描这些表一次。

我建议你使用MERGE子句:

merge into t1
using (
  select
    network.mid,
    decode(network.nid, 11, 10, 7) language_id,
  from
    profile,
    network
  where
    profile.status in ('A','U','S','H','I')
    and
    network.mid = profile.mid
) update_set 
on
  t1.mid = update_set.mid 
  and
  t1.is_deleted = 'N'
when matched
  then update set t1.language_id = update_set.language_id
;

Answer 2

检查MID列的索引和统计信息(我假设是高选择性)。查看执行计划。检查PQ/PDML。如果您的MID在所有方面都是UNIQUE列，您可以将其更改为:

UPDATE t1
SET t1.language_id = (SELECT distinct(CASE WHEN NETWORK.nid = 11 
                              THEN 10
                              ELSE 7
                          END)
                   FROM NETWORK
                   where NETWORK.mid = t1.mid
                   )
WHERE EXIST ( SELECT 1 from NETWORK where NETWORK.mid = t1.mid )
AND EXISTS( SELECT 1 FROM PROFILE where PROFILE.STATUS IN ('A','U','S','H','I')
                    AND PROFILE.mid = t1.mid )
AND t1.is_deleted = 'N'