Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Performance ON INSERT

SamFisherNov 17 2011 — edited May 10 2012
Hello All,

I have a question regarding performance of an insert.
1. INSERT INTO tbl1 SELECT * FROM tbl2 

2. Bulk insert. 
CUROSR c IS SELECT * FROM tbl1;
OPEN c 
LOOP 
FETCH c BULK COLLECT INTO plsql_tbl 
FORALL i IN  plsql_tbl.first..plsql_tbl.last
INSERT INTO tbl2 VALUES plsql_tbl(i);
EXIT WHEN c%NOT_FOUND; 
END LOOP;
CLOSE c;
Which one of the above 2 options is faster and optimal?
In option 2, we are selecting the data into cursor and then populating the cursor data into plsql table and then inserting the plsql data into a base table. Doesn't this take long time when compared to option 1, where it is just an Insert into select * ?

Thank You,
Shank.
This post has been answered by Billy Verreynne on May 9 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 7 2012
Added on Nov 17 2011
21 comments
910 views