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

Question

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.

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

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?

Answer 1

Have you considered an UPDATE FROM query?

Answer 2

In Oracle 9i and higher:

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

Answer 3

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.