英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。


评论