Home » SQL & PL/SQL » SQL & PL/SQL » Bulk insert and commit
Bulk insert and commit [message #316991] Tue, 29 April 2008 01:43 Go to next message
Messages: 33
Registered: August 2005
Location: Chennai

I want to insert millions of records into the a table using FETCH BULK COLLECT AND FORALL..INSERT. I am planning to limit 10000 records in bulk fetch and bulk insert. Is it good to issue commit after each bulk insert of 10000 records?.

Re: Bulk insert and commit [message #316997 is a reply to message #316991] Tue, 29 April 2008 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 65088
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT an expert question.

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Read them, follow them including what is said about formatting.

As you are an expert, I don't answer the question because you obviously already knows it.

Why not insert select?

Re: Bulk insert and commit [message #317219 is a reply to message #316997] Wed, 30 April 2008 01:16 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Take a look here.

And do a search on ORA-01555 to find out why interim commits are generally a bad idea. For your particular case though, I doubt it would be a problem.

Also, if you are determined to use an iterative approach, I would consider reducing your array size to 1000 or thereabouts. You won't notice the overhead of the smaller array, and it protects you from overfilling memory.

Ross Leishman
Previous Topic: How to "synchronize" procedures in PL/SQL
Next Topic: Restart DB after adding memory
Goto Forum:

Current Time: Tue Jul 25 18:07:21 CDT 2017

Total time taken to generate the page: 0.07427 seconds