我有兩個單獨的更新如下：

UPDATE mytab
SET colA = 1 
WHERE id IN ( SELECT id FROM mytab
             JOIN othertab ON mytab.otid = othertab.id 
             WHERE othertab.Lock = 'SomeLock' AND mytab.colA IS NULL);
UPDATE mytab
SET colB = 2 
WHERE id IN ( SELECT ID FROM mytab
             JOIN othertab ON mytab.otid = othertab.id 
             WHERE othertab.Lock = 'SomeLock' AND mytab.colB IS NULL);

我希望它們作為一個合併的 UPDATE 查詢。我所做的是：

UPDATE mytab
SET colA = 
CASE WHEN colA IS NULL THEN 1 ELSE colA END,
colB = 
CASE WHEN colB IS NULL THEN 2 ELSE colB END 
WHERE id IN ( SELECT id FROM mytab
                 JOIN othertab ON mytab.otid = othertab.id 
                 WHERE othertab.Lock = 'SomeLock');

一般來說，它在 SQL Server 和 Oracle 上都能滿足我的要求。我在這里關心的是合併查詢的性能。在該形狀中，它將更新按謂詞過濾後返回的每一行。有更好的解決方案嗎？

我對 Oracle 的了解不夠多，無法知道以下是否是有效的語法……或者是否有特定於該 RDBMS 的更巧妙的技巧。但是，在 SQL Server 上，我會做這樣的事情。

/** If you are updating small numbers of rows then this is probably good enough 
   on SQL Server.  Properly indexed tables, etc.
   **/
UPDATE mytab
SET colA = COALESCE(colA, 1)
   , colB = COALESCE(colB, 2)
WHERE EXISTS (SELECT TOP (1) 1 FROM othertab WHERE othertab.id = mytab.otid AND othertab.Lock = 'SomeLock')
   AND (   colA IS NULL
           OR colB IS NULL
           )
           
/** Larger numbers of rows could do better like this.
   **/
UPDATE M
SET colA = COALESCE(colA, 1)
   , colB = COALESCE(colB, 2)
FROM mytab AS M
   INNER JOIN othertab AS O ON O.id = M.otid AND O.Lock = 'SomeLock'
WHERE M.ColA IS NULL
   OR M.ColB IS NULL 

如果超過 20% 的行被修改，可能會有更好的方法。

甲骨文

merge into mytab a
using (
   select c.id
          ,coalesce( c.colA, 1 ) as colA
          ,coalesce( c.colB, 2) as colB
          -- Oracle performance might improve with NVL() instead of coalesce
   from mytab c
     join othertab d on c.otid = d.id
   where d.lock = 'SomeLock'
     and ( c.colA is null or c.colB is null )
) b
on ( a.id = b.id )
when matched then update set
   a.colA = b.colA
   ,a.qoh = b.colB;

• 提示可能有助於進一步 /*+ parallel */改進

  • 僅當新語句不夠快時才嘗試。

• DBMS_PARALLEL_EXECUTE可以幫助最小化 UNDO 空間

  • 不要將兩者混合

SQL 伺服器

{這裡放程式碼}

引用自：https://dba.stackexchange.com/questions/279547