oracle 如何提高 SET 涉及昂贵聚合子查询的 SQL UPDATE 语句的性能?

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/744058/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-18 18:03:30  来源:igfitidea点击:

How do I improve performance of a SQL UPDATE statement whose SET involves an expensive aggregate subquery?

sqloracleperformance

提问by Jordan Parmer

I have the following UPDATE scenario:

我有以下更新场景:

UPDATE destTable d
SET d.test_count = ( SELECT COUNT( employee_id )
                     FROM sourceTable s
                     WHERE d.matchCode1 = s.matchCode1 AND
                           d.matchCode2 = s.matchCode2 AND
                           d.matchCode3 = s.matchCode3 
                     GROUP BY matchCode1, matchCode2, matchCode3, employee_id )

I have to execute this in a loop changing out the match codes for each iteration.

我必须在循环中执行此操作,更改每次迭代的匹配代码。

Between two large tables (~500k records each), this query takes an unacceptably long time to execute. If I just had to execute it once, I wouldn't care too much. Given it is being executed about 20 times, it takes way too long for my needs.

在两个大表(每个表约 500k 条记录)之间,执行此查询所需的时间长得令人无法接受。如果我只需要执行一次,我就不会太在意。鉴于它被执行了大约 20 次,这对于我的需要来说太长了。

It requires two full table scans (one for the destTable and another for the subquery).

它需要两次全表扫描(一次用于 destTable,另一次用于子查询)。

Questions:

问题:

  1. What techniques do you recommend to speed this up?

  2. Does the SQL-optimizer run the subquery for each row I'm updating in the destTable to satisfy the where-clause of the subquery or does it have some super intelligence to do this all at once?

  1. 您建议使用哪些技术来加快速度?

  2. SQL 优化器是否为我在 destTable 中更新的每一行运行子查询以满足子查询的 where 子句,或者它是否具有一些超级智能来一次性完成所有这些?

回答by Quassnoi

In Oracle 9iand higher:

Oracle 9i及更高:

MERGE   
INTO    destTable d
USING   (
        SELECT  matchCode1, matchCode2, matchCode3, COUNT(employee_id) AS cnt
        FROM    sourceTable s
        GROUP BY
                matchCode1, matchCode2, matchCode3, employee_id
        ) so
ON      d.matchCode1 = s.matchCode1 AND
        d.matchCode2 = s.matchCode2 AND
        d.matchCode3 = s.matchCode3 
WHEN MATCHED THEN
UPDATE
SET     d.test_count = cnt

To speed up your query, make sure you have a composite index on (matchCode1, matchCode2, matchCode3)in destTable, and a composite index on (matchCode1, matchCode2, matchCode3, employee_id)in sourceTable

为了加快您的查询,请确保你有一个综合指数(matchCode1, matchCode2, matchCode3)destTable,并在综合指数(matchCode1, matchCode2, matchCode3, employee_id)sourceTable

回答by Joel Coehoorn

I have to execute this in a loop

我必须在循环中执行这个

The first thing you do is build the loop into your sub query or where clause. You're updating data, and then immediately replacing some of the data you just updated. You should be able to either filter your update to only change records appropriate to the current iteration or make your query complex enough to update everything in one statement- probably both.

您要做的第一件事是将循环构建到您的子查询或 where 子句中。您正在更新数据,然后立即替换您刚刚更新的一些数据。您应该能够过滤您的更新以仅更改适合当前迭代的记录,或者使您的查询足够复杂以更新一个语句中的所有内容 - 可能两者兼而有之。

回答by JayTee

Have you considered an UPDATE FROM query?

您是否考虑过 UPDATE FROM 查询?