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: 
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`
Have you considered an UPDATE FROM query?