Update a table based on changes to a table's timestamp with the same fields in both tables

Hi,

Thanks for repsonse. Sorry should've mentioned something been done similar to what you're describing.

The problem is that there are a feww 100 000 entries so taking about 10 hours to do an update because I understand it has to go through one record at a time, check the timestamp, and then update if required.

I'm told Oracle should be able to do this easily. This has now become my problem so turning to this forum for help.

Mark

Are you running this in PL/SQL code? Or just a SQL call?
Are your join columns indexed?

Hi,

I'm currently running this from sql dev but will migrate to a sql script once working.

there is a indexed column, call it "id" that is used to reference each record.

I've simplified the current method as follows:

update table_x x
set (
 x.field1,
 x.field2
 x.timestamp
)
= ( select
db2.field1,
db2.field2,
db2.timestamp
from table_y@db2 y
where y.id = x.id and db2.timestamp > x.timestamp)
where exists (select null from
table_y@db2 db20
where db20.id = table_x.id and db2.timestamp > x.timestamp);

db2 is the datbase link

What I think needs to be done is as follows:

find all entries in db2.table_y where the timestamp is greater than "a value" (and happy for now to just work with a value of say 1255906800 - i.e. today)

save these in an array or some form (?)

than iterate through these by id and check if the timestamp has changed, if so update the specified fields

so if initial query found 100 entries that had changed in table_y it only had to search through and do a comparison with 100 entries in table_x matched by the "id"

This is bit out of my depth but would certainly appreciate any help on how to do this better

Regards,
Mark

Hi, yes the ID columns are indexed.

So what you suggested looked to do exactly what I wanted, but when ran:

SQL Error: ORA-02064: distributed operation not supported
02064. 00000 -  "distributed operation not supported"
*Cause:    One of the following unsupported operations was attempted
           1. array execute of a remote update with a subquery that references
           a dblink, or
etc..

and from what I can tell there's no easy way around this. Creating a tempory table seems like an option but not when few 100 000 entries I guess.

I'm now trying to do using cursors and found this great link:
http://www.psoug.org/reference/array_processing.html

Fast Way 5 is what I'm looking at.

Thoughts? Is this the way to deal with this or is there a better solution.

Yeah, the ORA-2064 is a documented Oracle bug (if you have Metalink, search for 4311273.8 or Bug 4311273).

This issue is fixed in      

    * 10.2.0.3 Patch 13 on Windows Platforms
    * 10.2.0.4 (Server Patch Set)
    * 11.1.0.6 (Base Release)

If you can't/don't want to install the patch, another simple solution is to create a local copy of the remote table via CTAS, then process the data based on the local copy

i.e.,
create table table_y_local
as select * from table_y@db2 ;

yes patch update not possibe. What will this do to the system (overhead) when it has to create a local copy with a "million" entries?

If you create a local copy of the remote table (even if you only do "select field1, field2, field3" instead of "select *") there should be very little system overhead since there's almost no processing involved in the data transfer. If network latency is not an issue, the data transfer itself should not take very long.
 
You can simulate this process by using the all_objects view:

-- run this on the remote server
create table test_db_link
as select owner, object_name, object_id
from all_objects;
 
-- run this insert several times to have a fairly significant set of data
insert /*+ append */
into test_db_link
select owner, object_name, object_id
from all_objects;
 
-- run this on the local server, to create a copy of the remote table
create table test_db_link
as select *
from test_db_link@db_link;

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

Thanks alot! So is it possible to create a local copy based on a "select ..."? We don't need all records but only those that meet a requirement. The merge can then work off this which would be perfect.

Yeah, the select should run just fine and you shouldn't run into the aforementioned bug.

If you don't need all the records, it would be even easier:
select field1, field2, field3
from ...
where ...
(for example, only records with a recently modified timestamp)