This discussion is archived
12 Replies Latest reply: Nov 15, 2012 11:06 AM by Justin Cave RSS

Inserting  million row with commit...

user8983130 Newbie
Currently Being Moderated
Hi,

I need to insert almost million rows in my database.I have already split the row in separate files so that task would be easier. Now, i am planning to put commit after every 1000 line so that undo generation would be less and no locking would take place if i inserting those lines from multiple sessions.

But how can i insert commit after every 1000 line?? Any idea?

Regards,
  • 1. Re: Inserting  million row with commit...
    Purvesh K Guru
    Currently Being Moderated
    user8983130 wrote:
    Hi,

    I need to insert almost million rows in my database. I have already split the row in separate files so that task would be easier.
    That looks like a good move. You can use the SQL Loader to execute the job in parallel.
    Now, i am planning to put commit after every 1000 line so that undo generation would be less and no locking would take place if i inserting those lines from multiple sessions.
    That is a Very BAD Idea, IMHO.
    Never ever do an incremental commit. Commit is suggested to be used only once you have reached a Business milestone (a point of no return).

    Committing every 1000 records for 1M rows, you will end up committing 10,000,000/1,000 = 10,000 times.

    And reason for Incremental Commits is Locking, then it is wrong. Unless you have sessions accessing these data. Insert places a lock only on the Row that has been inserted, so there does not seem to be a reason to worry for it.
    You can ask the DBA to arrange for sufficient UNDO space, and by doing this you can get rid of the problems, you think they actually are.

    >
    But how can i insert commit after every 1000 line?? Any idea?
    Yes, there are. If you can provide the details of the Insert you wish to i.e. the Source, Method of Loading the data into database etc.

    PS:- Please do not forget to mention your Database version.
    select * from v$version;
  • 2. Re: Inserting  million row with commit...
    user8983130 Newbie
    Currently Being Moderated
    Thank you very much for your support.Those are insert command..like, insert into t values(1) in a sql extension file. I am planning to drag those files in cmd prompt and get works done. But i am still confuse how to inject commit after every 1000 or 10000 lines??

    DB version is:11.2.0.2
  • 3. Re: Inserting  million row with commit...
    Purvesh K Guru
    Currently Being Moderated
    user8983130 wrote:
    Thank you very much for your support.Those are insert command..like, insert into t values(1) in a sql extension file. I am planning to drag those files in cmd prompt and get works done. But i am still confuse how to inject commit after every 1000 or 10000 lines??

    DB version is:11.2.0.2
    If you are to execute it that way, I will suggest you to go for a Lunch Break while the Insert scripts are executed.

    A better way I would suggest is, to keep all the data into excel file into a Directory, and use SQL Loader/External Table to load it into database.

    You can google to get simple workable examples. You will just have to customize those to match your columns.

    Please do not commit while those are being loaded. I have used SQL Loader to load Millions of records, in one go, and it took just few seconds or maybe minute. And I guess you can sustain the Locks on the table for such duration. Hence, I have and will advice you to avoid using incremental COMMITS.

    if you are bent to use them, then put a COMMIT; after every 1000th line in the Insert Script.

    Choice will always be yours!!!
  • 4. Re: Inserting  million row with commit...
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    But i am still confuse how to inject commit after every 1000 or 10000 lines??
    vi my_file_with_inserts.sql
    :1000
    A
    [enter]
    commit;
    :2000
    A
    [enter]
    commit;

    and so on ...


    or, using awk
    cat my_file_with_inserts.sql|awk '{print $0; if(NR%1000==0){print("COMMIT;");}}'>my_file_with_inserts_and_commits.sql
    Edited by: user11181920 on Nov 15, 2012 10:31 AM
  • 5. Re: Inserting  million row with commit...
    sb92075 Guru
    Currently Being Moderated
    user8983130 wrote:
    Thank you very much for your support.Those are insert command..like, insert into t values(1) in a sql extension file. I am planning to drag those files in cmd prompt and get works done. But i am still confuse how to inject commit after every 1000 or 10000 lines??

    DB version is:11.2.0.2
    multiple COMMIT results in only 1 things; they make elapsed time to completion longer.
    multiple COMMIT should be avoided.
  • 6. Re: Inserting  million row with commit...
    L-MachineGun Pro
    Currently Being Moderated
    But....not enough commit can result in the famous "Snapshot too old" error.
    :p
  • 7. Re: Inserting  million row with commit...
    sb92075 Guru
    Currently Being Moderated
    L-MachineGun wrote:
    But....not enough commit can result in the famous "Snapshot too old" error.
    :p
    WRONG! WRONG! Wrong!
    Just the opposite, COMMIT inside LOOP is common cause for ORA-01555 Snapshot Too Old error!
  • 8. Re: Inserting  million row with commit...
    rp0428 Guru
    Currently Being Moderated
    >
    Just the opposite, COMMIT inside LOOP is common cause for ORA-01555 Snapshot Too Old error!
    >
    For updates yes - but is that true for inserts? Please explain - since for newly inserted rows there should be no read-consistency issues.
  • 9. Re: Inserting  million row with commit...
    user8983130 Newbie
    Currently Being Moderated
    Not clear..why multiple commit would result in longer completion??!!


    As per my knowledge ,when we would commit something in oracle it make the changes permanent which result in release in undo space after retention period.
    Snapshot is too old not gonna be a issue for us, coz this task is a part of migration process. We want to do this in minimal amount of time. And,to do so we are gonna perform the insertion from multiple session. And the repeated commit gonna help us to release the lock.
  • 10. Re: Inserting  million row with commit...
    Mark Williams-Oracle Employee ACE
    Currently Being Moderated
    Perhaps you would find the following AskTom thread (Issue Frequent COMMIT Statements) valuable:

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4951966319022
  • 11. Re: Inserting  million row with commit...
    Purvesh K Guru
    Currently Being Moderated
    You must read the article posted by Tom on Frequent Commits.


    Moreover, I fail to understand, what stops you from Trying? You will not crash your database by trying to insert a Million rows; Oracle is robust enough to handle many Millions (probably Billions, but never heard of it); Its a very meager amount from Oracle's perspective, even if its a huge number for you.

    Try and you will understand the pros and cons of each of the approaches.

    --Edit:-
    Mark has already provided the same link.

    Edited by: Purvesh K on Nov 16, 2012 12:31 AM
  • 12. Re: Inserting  million row with commit...
    Justin Cave Oracle ACE
    Currently Being Moderated
    user8983130 wrote:
    Not clear..why multiple commit would result in longer completion??!!
    Because a commit is a reasonably expensive operation. Doing more expensive operations means that you'll have a longer runtime.
    As per my knowledge ,when we would commit something in oracle it make the changes permanent which result in release in undo space after retention period.
    If all you are doing is issuing INSERT ... VALUES statements, there is, realistically, no issue with UNDO regardless of how frequently you commit because you would never need the information stored in UNDO.
    We want to do this in minimal amount of time. And,to do so we are gonna perform the insertion from multiple session. And the repeated commit gonna help us to release the lock.
    There is no lock to release.

    Doing a bunch of INSERT ... VALUES SQL statements is one of the slowest possible ways to load data. Adding commits slows it down even more but doing a million INSERTs without using bind variables is going to be painful and has a high probability of overloading your shared pool leading to errors. That's one of the reasons that doing interim commits is a really bad idea. When this process errors out (and it's pretty likely that it will), you certainly don't want to be in a position of trying to figure out which changes were made and committed, which were made and rolled back, and how to restart the process appropriately.

    If you are at all interested in efficiency, I would strongly suggest reconsidering the entire approach. It will be much more efficient (and much less likely to generate an error) if you were to use expdp and impdp (the DataPump versions of the export and import utilities) if the source of the data is an Oracle database or SQL*Loader/ external tables with a flat file of data in order to load the data into the database. That will save you from issuing a million non-sharable SQL statements and flooding your shared pool. Those utilities also have built-in support for parallelization so you're not trying to manually break them up into multiple files that can be run from multiple sessions. Even without parallelism, though, loading a million rows of data using a single SQL*Loader thread should take maybe a couple of minutes on a decent laptop so it's probably not even worth trying to improve performance during a migration unless you really, really care about saving every possible second.

    Justin

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points