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

I have the following UPDATE scenario:

我有以下UPDATE方案:

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:

What techniques do you recommend to speed this up?

您建议采用哪些技术来加快速度?
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?

SQL优化器是否为我在destTable中更新的每一行运行子查询以满足子查询的where子句,或者它是否具有一些超级智能来同时执行此操作?

3 个解决方案

#1

In Oracle 9i and 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

要加快查询速度,请确保destTable中有(matchCode1,matchCode2,matchCode3)复合索引,sourceTable中有(matchCode1,matchCode2,matchCode3,employee_id)的复合索引。

#2

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子句中。您正在更新数据,然后立即替换刚更新的一些数据。您应该能够过滤更新以仅更改适合当前迭代的记录,或使您的查询足够复杂以在一个语句中更新所有内容 - 可能两者都有。

#3

Have you considered an UPDATE FROM query?

您是否考虑过UPDATE FROM查询?

#1