Best way to update/insert into a table based on a remote table.
- by martilyo
I have two very large enterprise tables in an Oracle 10g database. One table keeps the historical information of the other table. The problem is, I'm getting to the point where the records are just too many that my insert update is taking too long and my session is getting killed by the governor.
Here's a pseudocode of my update process:
sqlsel := 'SELECT col1, col2, col3, sysdate
FROM table2@remote_location dpi
WHERE (col1, col2, col3) IN
(
SELECT col1, col2, col3
FROM table2@remote_location
MINUS
SELECT DISTINCT col1, col2, col3
FROM table1 mpc
WHERE facility = '''||load_facility||'''
)';
EXECUTE IMMEDIATE sqlsel BULK COLLECT
INTO table1;
I've tried the MERGE statement:
MERGE INTO table1 t1
USING (
SELECT col1, col2, col3 FROM table2@remote_location
) t2
ON (
t1.col1 = t2.col1 AND
t1.col2 = t2.col2 AND
t1.col3 = t2.col3
)
WHEN NOT MATCHED THEN
INSERT (t1.col1, t1.col2, t1.col3, t1.update_dttm )
VALUES (t2.col1, t2.col2, t2.col3, sysdate )
But there seems to be a confirmed bug on versions prior to Oracle 10.2.0.4 on the merge statement when doing a merge using a remote database. The chance of getting an enterprise upgrade is slim so is there a way to further optimize my first query or write it in another way to have it run best performance wise?
Thanks.
