Home » SQL & PL/SQL » SQL & PL/SQL » bulk collect
bulk collect [message #414357] Tue, 21 July 2009 13:56 Go to next message
bella13
Messages: 90
Registered: July 2005
Member
HI I need some help with a huge insert in a table. Around 5million rows

1. I read data out of a view which returns around 5 million rows. There are around 30 fileds in the view.
2. This data that i read out of the view i need to insert in a table.

Right now i am commiting on the table based on comapny name. For every company name i do a commit. But its not performing well. What is the best way to do this insert.

There was a mention of bulk commit- so do i havve to define types for all the 30 fields?
Any information will be helpful
Thanks,

bulk collect and commit frq [message #414358 is a reply to message #414357] Tue, 21 July 2009 13:58 Go to previous messageGo to next message
bella13
Messages: 90
Registered: July 2005
Member
HI I need some help with a huge insert in a table. Around 5million rows

1. I read data out of a view which returns around 5 million rows. There are around 30 fileds in the view.
2. This data that i read out of the view i need to insert in a table.

Right now i am commiting on the table based on comapny name. For every company name i do a commit. But its not performing well. What is the best way to do this insert.

There was a mention of bulk commit- so do i havve to define types for all the 30 fields?

And more importantly can bulk collect handle commit at the end of 5 million rows. Or how would i specify the limit on commit frequencey?

Any information will be helpful
Thanks,

Re: bulk collect [message #414359 is a reply to message #414357] Tue, 21 July 2009 14:00 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>HI I need some help with a huge insert in a table. Around 5million rows
5 Million is not a large number

INSERT INTO NEW_TABLE SELECT * FROM OLD_TABLE;
COMMIT;

Above is fastest solution.
Re: bulk collect [message #414365 is a reply to message #414357] Tue, 21 July 2009 15:13 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
bella13 wrote on Tue, 21 July 2009 14:56

Right now i am commiting on the table based on comapny name. For every company name i do a commit. But its not performing well.



Are you using PL/SQL then to do this? BlackSwan's suggestion is the best way. Use only SQL.

And what is performing poorly, the insert or the select from the view? If it's the INSERT, then maybe you have constraints or lots of indexes or triggers. If it's the SELECT, then your view might have poor joins or old statistics.
Re: bulk collect [message #414404 is a reply to message #414357] Wed, 22 July 2009 00:46 Go to previous messageGo to next message
saptarshibasu
Messages: 15
Registered: July 2009
Junior Member
Follow this thread: http://www.orafaq.com/forum/t/147000/143330/

Try to use SQL instead of PLSQL constructs as suggested above. If you cannot fit your logic in a single SQL and you HAVE TO go for a PL/SQL cursor loop,
1. Try using BUL COLLECT, FORALL syntax. this will reduce the number of fetches based on your array size. Please note if you are using Oracle 10g, default arraysize is 100.
2. Also committing after every row is not a good idea. When you commit LGWR wakes up immediately to write the data from redo log buffer to the online redo log files. So, this slows down the system. Therefore, commit after inserting atleast 1K or 5K or 10K records whichever performs well.
Re: bulk collect [message #414446 is a reply to message #414404] Wed, 22 July 2009 04:06 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just commit once, at the end.
The whole thing is one transaction => one commit.
Intermediate commits just mean that if you get an error then you have to write some more complex code to finish the job while ignoring the records that have already been inserted.
Previous Topic: Problem with Record datatype
Next Topic: Multiple call for procedure in same time from its main Procedure
Goto Forum:
  


Current Time: Fri Dec 09 09:42:49 CST 2016

Total time taken to generate the page: 0.04952 seconds