I have this query that updates values in a Oracle schema using a datalink to retreive values from a table that exists in another schema.
UPDATE SCHEMA1.TABLE1 t1 SET NAME =
(SELECT t2.NAME FROM SCHEMA2.TABLE2@DATA_LYNK_TEST t2 WHERE t1.ID= t2.ID)
TABLE1.ID is a primary key. TABLE2.ID is a primary key.
My question : Will this query use the index on TABLE2.ID when going through the data link? If so, is there anything on the datalink thats needs to be enabled to make the index usable?
The explain plan I get doesn't help me much.
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
---------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 147M(100)| | | |
| 1 | UPDATE | TABLE1 | | | | | | |
| 2 | TABLE ACCESS FULL| TABLE1 | 36M| 1648M| 224K (1)| 00:00:09 | | |
| 3 | REMOTE | TABLE2 | 1 | 106 | 1 (0)| 00:00:01 | DATA_~ | R->S |
---------------------------------------------------------------------------------------------------------------
I was expecting a FULL table scan on TABLE1 since I am updating the entire table. But I how do I interpret the 3rd line of the explain plan? Is it a full table scan on TABLE2 for every line updated in TABLE1?
