bulk collect …for all usage

问题:

I want to understand the usage and need for bulk collect forall statements.

An example mentioned here

In most examples in different web pages; authors first fetch data from a table by using bulk collect statements. After that, they are inserting it into target table by using the forall statement.

DECLARE
TYPE prod_tab IS TABLE OF products%ROWTYPE;
products_tab   prod_tab := prod_tab();
 BEGIN
-- Populate a collection - 100000 rows
SELECT * BULK COLLECT INTO products_tab FROM source_products;

FORALL i in products_tab.first .. products_tab.last
 INSERT INTO target_products VALUES products_tab(i);

But I do not understand when to choose this method. I think I can write the code like below:

INSERT INTO target_products
SELECT * FROM SOURCE_PRODUCTS;

And I think, insert into select statement can work more performance.

So why we need to choose bulk collect forall statements? For error logging or any other advantages?

回答1:

If you can implement your requirement by an INSERT INTO SELECT clause, you don't even need to use forall and bulk collect statements. There is a saying, "if you can do it in SQL, do it in SQL".

But in some situations, you may need to process your data row-by-row, which can force you to code a loop. This is actually terrible, it means that your operations within that loop will be executed as single statements over an over. But if you use forall, PL/SQL engine will run your loop in a set-based fashion, which would give you a real good performance boost.

回答2:

Well, the answer to your question is "when ever you possibly can !".

The problem with your question is that in the scenario you described you don't even need plsql.

forall is for situations you need to perform some operations with plsql on the data you retrieved, before you insert it to the target table. in this case you'l have a large amount of data in a plsql collection that you would want to bulk insert into the target table. this is what forall is for and it's much more efficient then insert in a loop.

回答3:

Here is a nice article about the use of bulk collect and forall:

http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html

回答4:

Whenever, we are submitting PL/SQL blocks into oracle server always SQL statements are executed. Through SQL engine and also procedural statements are executed. Through Procedural statement executor. This procedural statement executor is available in PL/SQL engine, whenever we are using large amount of loading through SQL, PL/SQL statements always oracle server executes these statements separately through these engines. This type of execution methodology always content switching execution methodology degrades performance of the application. To overcome this problem, oracle introduced “bulk bind” process using collections, i.e. in this method oracle server executes all SQL statements at a time.

1. select…into…clause
2. cursor fetch statement
3. DML returning clauses

PL/SQL Bulk Collect And Bulk bind

For more information >>>>>>>http://www.oraappdata.com/2017/02/plsql-bulk-collect-and-bulk-bind.html