DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: help with insert...

  1. #1
    Join Date
    Jul 2000
    Posts
    31
    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;
    /

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width