how often/where do i need to place commit statements? if i'm inserting 10 rows should i commit after each one or after they are all inserted? what about 100000 rows? what about deleting rows? if i'm creating a table or sequence do i need to commit after creating them? what about dropping? any help would be appreciated.. i've had a procedure running for 1.5 hours now and wondering if it could have something to do with using excess memory because of my commit points
Generally you want to commit when the record group you're inserting / altering etc is complete. This way you maintain data integrity in the event that there is an error. However if your inserting many rows, you should probably try to create some application logic to segment your inserts. Something like breaking up 100,000 inserts into groups of 1000, or something similar. This way you free up memory, and reduce the risk of something breaking your transaction. Seriously if your entire transaction is going to take more than 1 hour, you need to be looking at the chances of something interrupting it, which if this is something that you perform very often, it's likely that it will get interrupted some time. Deleting is slightly different because as long as you are deleting based on some measurable criteria (an ID, date range etc...), your deletes don't necessarily need to be run as a transaction at all. This would really depend on the affect on your application if data is retrieved and only half of the records have been deleted.