I have two tables weblog@stats and weblog@stats2. I am currently connected to the stats2 database instance.
Weblog@stats - 5.4 million rows
Weblog@stats2 - 4.68 million rows
I want to get both tables in sync. I am using the following code. Is there a more efficient way for me to achieve this?
cursor c1 is
select * from weblog@stats
where seqnum not in (select seqnum from weblog);
for rec in c1 loop
insert into weblog(URL, UNIVERSITY, SERVER_NAME, SEQNUM)
values(rec.URL, rec.UNIVERSITY , rec.SERVER_NAME , rec.SEQNUM);
- Bulk inserts are faster than single inserts. Of course, the problem is that you may blow your logs if you try to insert 1 million records, I don't know. See if you can have them make you a special rollback segment (or whatever) for this statement, because the bulk insert will be *much* faster.
- NOT IN's are generally not very efficient. The outer-join trick should be used instead.
Here is a statement that incorporates both suggestions:
- URL ,
- UNIVERSITY ,
- SERVER_NAME ,
- W1.URL ,
- W1.UNIVERSITY ,
- W1.SERVER_NAME ,
- WEBLOG@STATS W1,
- WEBLOG W2
- W2.SEQNUM (+)= W1.SEQNUM AND
- W2.SEQNUM IS NULL