Home » SQL & PL/SQL » SQL & PL/SQL » BULK INSERT PERFORMANCE (ORACLE 10G)
BULK INSERT PERFORMANCE [message #575791] Sun, 27 January 2013 23:50 Go to next message
mvrkr44
Messages: 11
Registered: December 2012
Junior Member
I HAVE CREATED ONE TABLE AND ALSO I HAVE CREATED ONE INDEX ON IT.

CREATE TABLE TEST (EMP NUMBER);
CREATE INDEX EMPID ON TABLE(EMP);

NOW I HAVE CREATED ONE PL SQL BLOCK WITH BULK INSERT ON TEST TABKLE TO INSERT LAKHS OF RECORDS...
WHETHER THE PERFORAMCE WILL DEGRADE OR NOT WHEN COMPARED WITH NORMAL INSERT?

AND ALSO EXPLAIN THE MAIN DIFFERENCE BEWTWEEN INSERT & BULK INSERT.

[EDITED by LF: fixed topic title typo; was "BUULK"]

[Updated on: Mon, 28 January 2013 11:21] by Moderator

Report message to a moderator

Re: BUULK INSERT PERFORMANCE [message #575792 is a reply to message #575791] Sun, 27 January 2013 23:55 Go to previous messageGo to next message
BlackSwan
Messages: 20087
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: BUULK INSERT PERFORMANCE [message #575793 is a reply to message #575791] Mon, 28 January 2013 00:03 Go to previous messageGo to next message
a_oracle
Messages: 85
Registered: November 2010
Member
What did you observe from your test case? Did you find any difference?
And of course needless to say, always and always prefer single insert statements. Its faster and simpler than any pl/sql block which involve loops..commits inside loops etc. Moreover, loops means row by row processing and in turn means slower.
And for the difference, you can just google around and you might find a lot on this topic.

Report message to a moderator

Re: BUULK INSERT PERFORMANCE [message #575794 is a reply to message #575793] Mon, 28 January 2013 00:09 Go to previous messageGo to next message
mvrkr44
Messages: 11
Registered: December 2012
Junior Member
my main concern is if we are inserting data against the indexed coumn which one is faster(single insert or bulk insert )?

Report message to a moderator

Re: BUULK INSERT PERFORMANCE [message #575806 is a reply to message #575794] Mon, 28 January 2013 01:36 Go to previous message
Michel Cadot
Messages: 54142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Single INSERT is always faster than any PL/SQL.
But for bulk insert (insert many rows), it is better to create the index AFTER (or to make it unusable before and rebuild it after).

Regards
Michel

Report message to a moderator

Previous Topic: Quarter year
Next Topic: ORA-55617: Flashback Archive
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon May 20 09:24:55 CDT 2013

Total time taken to generate the page: 0.11548 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.