Code 1 does this
1. Select rows from table (products)
2. Load the rows into a collection (products_tab) in expensive PGA
3. Picks the records from the collection (products_tab)
4. Inserts the data from collection (products_tab) into the table (products_history)
Code 2 does this
1. Select rows from table (products)
2. Do a direct path insert into the table (products_history)
Now you tell me which one will be faster?
Thanks for your reply..
Consider I am having 100 millions records and code2 is something like below...(added begin and end block)
INSERT /*+ APPEND*/ INTO products_history (product_id,product_name)
select product_id,product_name from products;
now pls advice me which one give the better performance and Why ?
As above scenarios, code 2 (using direct path INSERT) will be faster then code 1.
As in direct path, the database appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. But Free space of table will not be re-used.
But code 1 will follow conventional INSERT along with context-switching between SQL/PLSQL engines for execution and will take more time in comparision. But Free space of table will be re-used.
Refer documentation for more:- http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables004.htm
The only place I'd use BULK COLLECT and FORALL inserts would be if there was some heavy PL/SQL code processing the data that couldn't be done using SQL alone.
99.999% of the time, using pure SQL is the correct way. I've rarely had need to use BULK COLLECT methods, even though I know perfectly well how to use them, and I've written many years worth of code.
In my project ...they have used BULK COLLECT INSERT many places..
Yes; those are real time examples of using bulk collect and you need a complete work through of it.
As there might be many reasons in your project to code in plsql as to implement business logic and data processing/manipulation before and after bulk collect! As Blushadow has already notified that it requires in case of heavy data processing or something cannot be possible without plsql. Even all those programs are not only doing just a single job of bulk insertion!
So don't go blindly and replace everywhere where you find it. First analyze, where direct-path insert will be fit in(Like:- loading metadata from data files using SQL Loader) and there will be no impact to other inter-connected sub-systems after change, talk with experience colleagues( who know the full system more; as there are not fool sitting around and don't find use of bulk collect slowing down performance and can be replaced by some other way!
Remember one thing, here just take all right ideas, hints, concepts and understand all things as how all work in better way! Then you be the better person from rest of us to know how your system talks and use the best solution fit in at last!
Suppose you are writing an INSERT statement inside a LOOP which is executing 10,000 times.
There the performance of pure SQL will be impacted.Because , the single SQL statement will be sent to server for processing 10,000 times.
In that case , BULK collect is more powerful in terms of performance.
If you're writing an INSERT statement in a loop that executes 10,000 times you've written bad code in the first place. What data are you inserting? Where has it come from? If it's from the database you shouldn't be doing it in a loop in the first place, it should be a single INSERT...SELECT... statement.
Yes, BULK COLLECT and FORALL is better than row by row processing, but better still is pure SQL statements, so that doesn't answer the OP's question as to what scenarios it would be good to use BULK COLLECT methods, it just says that bulk collect is better than a poorer method (a method which isn't pure SQL as you suggest)