Problem in inserting large data
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Problem in inserting large data

  1. #1
    Join Date
    Oct 2000
    Posts
    211
    Hi Friends,
    I am trying to insert 1 million records using the following statement:

    insert into tableA
    select col1,col5,col3 from tableB b, tableC c
    where b.col4=c.col4;

    However as soon as I give the command to execute, the SQL PLUS starts hanging.
    Should one take any extra steps/measures when doing large insertions?
    I am running 8.0.5 on a deck alpha machine
    Thanks
    manjunath


  2. #2
    Join Date
    Jun 2000
    Posts
    417
    How large is the insertion? You could do it in pl/sql and just commit every 1000 records or so. That would also be a lot nicer to your rollback segments, which might by why sqlplus is hanging. Check the alert log and you might see some unable to extend errors.

    In any case here's a sample pl/sql block
    <font face="courier">
    declare
    &nbsp;&nbsp;cursor cur is select col1, col5, col3
    &nbsp;&nbsp;from tableB b, tableC
    &nbsp;&nbsp;c where b.col4=c.col4;
    &nbsp;i NUMBER;
    begin
    &nbsp;&nbsp;i = 0;
    &nbsp;&nbsp;for rec in cur loop
    &nbsp;&nbsp;&nbsp;&nbsp;insert into tableA values (rec.col1, rec.col5, rec.col3);
    &nbsp;&nbsp;&nbsp;&nbsp;if ( mod(1,1000) == 0 ) then
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;commit;
    &nbsp;&nbsp;&nbsp;&nbsp;end if;
    &nbsp;&nbsp;&nbsp;&nbsp;i := i+1;
    &nbsp;&nbsp;end loop;
    &nbsp;&nbsp;commit;
    end;

    that's off the top of my head so syntax is probablly shaky but you get the idea.

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    1. Make sure your base query is indexed well.
    2. use the APPEND hint (http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76992/hints.htm#19039)
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    OK, so how did you get your code to indent on this Forum?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Oct 2000
    Posts
    211

    Thanks pwoneill
    I ran the following , and still the sql plus hangs.

    declare
    cursor cur is select col1, col5, col3
    from tableB b, tableC
    c where b.col4=c.col4;
    i NUMBER;
    begin
    i := 0;
    for rec in cur loop
    insert into tableA values (rec.col1, rec.col5, rec.col3);
    if ( mod(1 ,1000) = 0 ) then
    commit;
    end if;
    i := i+1;
    end loop;
    commit;
    end;

    Should I try to assign a bigger rbs?
    Presently I have 4 rbs of 4mb each.
    Thanks
    manjunath


  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    What do you mean by "SQL*Plus hangs"? If it returns an error then let us know what it is. If it simply does not return control that probably mean it is still working. Inserting large dataset might take a lot of time, maybe you simply are not patient enough.

    Connect with another Sql*plus session and select count(*) from tableA. If you realy are commiting periodically then you should be able to find this number increasing in batches of 100.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Oct 2000
    Posts
    211
    I checked up in task manager. And it says sql plus is not responding.
    Thanks
    manjunath

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    But this does not mean the insertion is not taking place! Sql*Plus passes comand (either a query or a PL/SQL block to Oracle Server and waits there to get the control back. But the database will not return control to it until the whole comand is executed. In your case SQL*Plus will probably respond only after all the rows are inserted into TableA.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    not respondoing doesnt mean it hangs, do you know how to query v$session_wait? if you know find the sid of the user inserting from v$session and then

    select event from v$session_wait
    where sid=sid_of_user_inserting

    This will show what is going on in that session

  10. #10
    Join Date
    Jun 2000
    Posts
    417
    Spacing with judicious use of nbsp :)

    As already pointed out just because it's hanging doesn't mean it's not doing anything. How many rows are being inserted?

    <font face="courier">
    select count(1) from tableB b, tableC c where b.col4 = c.col4;
    </font>;

    Did you check your alert logs to see if it's actually an RBS extending problem?

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