SQL server to Oracle Data Update performance issue.

It is possible that update from the join between Oracle and SQL Server tables is the root cause of the problem with performance. It just takes long time to update 25 million rows from the distributed join like this. If this is a one-time action then it could be better to consider the following:

1. Create table in Oracle which includes just 2 columns (to store ID and Column values from the SQL Server table).

2. From SQL Server: use openquery to insert the data from SQLTable to newly created Oracle table

3. From Oracle: update OracleTable records from the join with this newly created table

This way, the actual update statement will be running in Oracle and it will be the update from the join of 2 Oracle tables. Here are the scripts:

From Oracle (create table):

 /* use whatever appropriate types/names */
 create table SQLTableInOracle (ID number, Column varchar2(100)) 

From SQL Server (insert data, this should be fast with only 145 rows to insert):

 insert openquery([LinkedServerName], 'select * from SQLTableInOracle')
 select ID, Column from SQLTable;
 go

Finally, from Oracle, update from the join (I hope I have typed the Oracle flavour of the statement to update from join correctly):

 update 
 (
     select
         o.Column as OldColumn, s.Column as NewColumn
         from OracleTable o inner join SQLTableInOracle s
             on o.ID = s.ID
 ) t
 set t.OldColumn = t.NewColumn;
 commit;

Hope this helps.

Oleg