-
Hi,
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?
Thanks.
declare
cursor c1 is
select * from weblog@stats
where seqnum not in (select seqnum from weblog);
begin
for rec in c1 loop
insert into weblog(URL, UNIVERSITY, SERVER_NAME, SEQNUM)
values(rec.URL, rec.UNIVERSITY , rec.SERVER_NAME , rec.SEQNUM);
commit;
end loop;
end;
/
-
Well, a few points:
- 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:
-INSERT INTO
- WEBLOG
- (
- URL ,
- UNIVERSITY ,
- SERVER_NAME ,
- SEQNUM
- )
- SELECT
- W1.URL ,
- W1.UNIVERSITY ,
- W1.SERVER_NAME ,
- W1.SEQNUM
- FROM
- WEBLOG@STATS W1,
- WEBLOG W2
- WHERE
- W2.SEQNUM (+)= W1.SEQNUM AND
- W2.SEQNUM IS NULL
HTH,
- Chris
