优化使用MAX()的更新查询。

huangapple go评论49阅读模式
英文:

Optimize update query which uses MAX()

问题

这个问题是针对Oracle 19数据库的。

我需要一些帮助来优化这个更新语句:

UPDATE T1 T1
   SET T1.COL1 = (SELECT MAX(T2.COL1)
                   FROM T2 T2
                   WHERE T2.COL2 = T1.COL2)
 WHERE T1.COL2 BETWEEN :1 AND :2
   AND T1.COL1 IS NULL
   AND EXISTS
 (SELECT 1
          FROM (SELECT MAX(T2.COL1) 
                  FROM T2 T2
                 WHERE T2.COL2 = T1.COL2) V1
         WHERE V1.COL1 IS NOT NULL)

我喜欢这个答案:

https://stackoverflow.com/questions/65829430/massive-update-vs-merge-performance-on-oracle

我尝试了类似的方法,但我的问题是我的更新语句中有MAX()函数,我不知道如何优化它。

英文:

This is question is for Oracle 19 DB.
I need some help optimizing this update statement:

UPDATE T1 T1
   SET T1.COL1 = (SELECT MAX(T2.COL1)
                   FROM T2 T2
                   WHERE T2.COL2 = T1.COL2)
 WHERE T1.COL2 BETWEEN :1 AND :2
   AND T1.COL1 IS NULL
   AND EXISTS
 (SELECT 1
          FROM (SELECT MAX(T2.COL1) 
                  FROM T2 T2
                 WHERE T2.COL2 = T1.COL2) V1
         WHERE V1.COL1 IS NOT NULL)

I like this answer:

https://stackoverflow.com/questions/65829430/massive-update-vs-merge-performance-on-oracle

and I tried doing similar but my problem is that my update has MAX() and I don't know how to optimize it.

答案1

得分: 2

"Merge",如您所说:

merge into t1 
  using (select t2.col2, 
                max(t2.col1) max_col1
         from t2
         group by t2.col2
         having max(t2.col1) is not null
        ) x
  on (t1.col2 = x.col2)
  when matched then update set
    t1.col1 = x.max_col1
  where t1.col2 between :1 and :2
    and t1.col1 is null;

请注意,这是您提供的SQL代码的翻译部分。

英文:

Merge, as you said:

merge into t1 
  using (select t2.col2, 
                max(t2.col1) max_col1
         from t2
         group by t2.col2
         having max(t2.col1) is not null
        ) x
  on (t1.col2 = x.col2)
  when matched then update set
    t1.col1 = x.max_col1
  where t1.col2 between :1 and :2
    and t1.col1 is null; 

答案2

得分: 1

使用具有HAVING子句的MERGE语句来执行V1.COL1 IS NOT NULL过滤器(或者在GROUP BY之前使用WHERE子句,作为替代方法):

MERGE INTO T1
USING (
  SELECT col2,
         MAX(col1) AS col1
  FROM   t2
  -- WHERE col1 IS NOT NULL
  GROUP BY col2
  HAVING MAX(col1) IS NOT NULL
) t2
ON (t1.col2 = t2.col2)
WHEN MATCHED THEN
  SET   T1.COL1 = t2.col2
  WHERE T1.COL2 BETWEEN :1 AND :2
  AND   T1.COL1 IS NULL;
英文:

Use a MERGE statement with a HAVING clause to perform the V1.COL1 IS NOT NULL filter (or, alternatively, a WHERE clause before the GROUP BY):

MERGE INTO T1
USING (
  SELECT col2,
         MAX(col1) AS col1
  FROM   t2
  -- WHERE col1 IS NOT NULL
  GROUP BY col2
  HAVING MAX(col1) IS NOT NULL
) t2
ON (t1.col2 = t2.col2)
WHEN MATCHED THEN
  SET   T1.COL1 = t2.col2
  WHERE T1.COL2 BETWEEN :1 AND :2
  AND   T1.COL1 IS NULL;

huangapple
  • 本文由 发表于 2023年7月12日 20:49:31
  • 转载请务必保留本文链接:https://go.coder-hub.com/76670758.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定