Oracle update bulk collect from cusor

Developer, Oracle expert, performance enthusiast and genuine technology geek. View all posts by Gerald. Like Like. Hi Venzi, I have a question on Bulk and regular insert. Can you tell me which one is better? Writing select insert or Bulk insert? This very heplful. What is the reason of such a vast performance difference between the Table Collection loop and Cursor loop?

Do you have any detailed information about it? In the cursor Oracle must refresh the binds every loop with the new data. Also the Insert is executed each time! No need to refresh something again and again. No soft parses, reading of the new values of the binds…. Can you tell us what version of Oracle you ran this test on? In fact, all the performance benefit is gained by the latter change and none by the former.

Hi I am using Bulk collect for parameterised cursors. Plz can u suggest me can we use bulk collect in different loops or is there any constraints in using them. The FOR loop was provided — as with other languages — to simplify code and provide structure. For example, if I want to process a bunch of rows multiple times I can read them into a collection first.

It depends on the use cases anyway. So it depends also here on the developer how good the code is written! Did you bounce the database between your tests?

Flushing the buffer cache would be insuffficient in this case:. Perhaps we have done this to death but I am a little confused. In my cold run I just executed both procedures so that buffer pool and shared pool are filled. I would say that it depends on the use case and the benefit for the developer — so the developer has to know when to use one of these clauses. Then it would be better to fill the collections with bulk collect rather than write a for loop and and filling the collections by yourself.

Well, that will be one of the standard scenarios I guess! Thank you for this test, very interesting. Did you perform the test on a working database?

Also, I don't know how to update two rows at once in that variant. Could anyone help out? Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Asked 3 years, 3 months ago. Rene A. October 08, - pm UTC. Tom, Thanks for all your inputs here. I read that this works for a single column. The only way i figured is declaring multiple collections one for each column and fetching individually.

January 22, - am UTC. Tom Bear with me on this, it does have relevance to this thread eventually! There are a few ways i can do this but I thought that one of the best ways would be to use a separate stored procedure for each sql statement ie supports modularised code then using a ref cursor return to a calling procedure each resultset. Then the calling procedure could take each resultset and build up an index-by-integer table or something similar in memory ready to insert into the real table.

Questions are 1 Assuming I went with this method, I can't figure out how to "glue" each resultset together each one contains a primary key so that all the records line up with the correct data ready to do the insert into the real table. Or would it be better to create one huge stored procedure with all the sql resultsets unioned together before loading into the real table?

Please can you help. Many thanks. January 27, - am UTC. You are doing joins. It is all about inline views at that point. So my question 2 should read: 2 Is this method the best way?

Or would it be better to create one huge stored procedure with all the sql resultsets JOINED together before loading into the real table? Thanks for the help on this. The join condition from the first select statement: c.

January 28, - am UTC. Tom, As soon as i got this error while development , i searched in your site. In this thread you have mentioned it is fixed in 8.

But i have the same invalid cursor error in 8. Please see as below and explain do we need to apply any other patch or something. Test case. October 29, - am UTC. We are assuming that there are two tables — Customer information — Which is the name of customer visiting to specified E commerce website. There are millions of records in customer table but we are assuming that there are records in customer table.

We need to execute all the ways and need to check the e performance of the different update statements. Scenario 1 : Traditional way to update individual records and commit after for loop. If you can check the first example where we are trying to update all records using for loop and then use commit statement.

It is taking around 2 mins and 42 seconds to complete the procedure and to update the records. Scenario 2 : Traditional way to update individual records and commit inside for loop. If we can check the process and timing to execute the records or update the records in database it is approximately 3 mins and 58 seconds.