Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Bulk Insert vs Insert Into ... Select From
- Thread starter Dendarii
- Start date
My choice is INSERT..SELECT, because it's free from context switching. In general pure sql always beats pl/sql in performace. Though if you have rollback issues or need some intermediate processing you have to use pl/sql.
Regards, Dima
Regards, Dima
Hi Dima
What do you mean by ”if you have rollback issues…”?
RBS is created based on DML on rows in the table – since the changes are the same (the result is the same – only the script is different) – the RBS is the same.
I agree with you that if possible always use pure DML statements – even if you have to “go around” because you can’t do it in one statement.
The Bulk… is some pl/sql situations where Oracle does it as pure as possible. And the speed is also fast.
Regards
Allan
Icq: 346225948
What do you mean by ”if you have rollback issues…”?
RBS is created based on DML on rows in the table – since the changes are the same (the result is the same – only the script is different) – the RBS is the same.
I agree with you that if possible always use pure DML statements – even if you have to “go around” because you can’t do it in one statement.
The Bulk… is some pl/sql situations where Oracle does it as pure as possible. And the speed is also fast.
Regards
Allan
Icq: 346225948
I meant that if RB is not properly sized some extra time (quite large sometimes!) would be spent for expanding it to fit a large transaction. Another issue is disk space shortage. In this case if ACID is not a mandatory requirement, the whole work may be done in smaller batches (transactions) with the use of bulk operations.
Regards, Dima
Regards, Dima
- Status
