Using index in merge update in Oracle

Question

I have query like this:

MERGE INTO table1 t1 USING
  (SELECT t2.id ,
          t2.updated ,
          t2.data
   FROM table2 t2) sel ON (sel.id = t1.id
                           AND sel.updated = t1.updated) WHEN MATCHED THEN
UPDATE
SET t1.data = sel.data;

In plan I see INDEX FULL SCAN on table2 and TABLE ACCESS FULL on table1. Then this tables join using HASH JOIN. Both tables have indexes including id, updated and data fields.

Is there any way to remove TABLE ACCESS FULL and use faster way to access table1?

score 1 · Accepted Answer · answered Oct 04 '21 at 21:47

Is the goal to change the plan or to improve the performance? If you're going to need to read every row from table1, a table scan is a pretty efficient way to do that. It sounds like you're reflexively equating table scans with poor performance which isn't generally true-- table scans can be quite efficient in these sorts of contexts.

If there was an index on table1( id, updated, data ) and that index was smaller than the table (i.e. there are other columns in table1 that aren't involved in the query), I'd guess that the optimizer would choose to do a full scan on that index as well. That's probably not going to generate a much more efficient query but it would remove the table scan.

Using index in merge update in Oracle

1 Answers1