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