CODEWITHSUNDEEP
oracle-databaseplsqlbulkinsert
user125687
user125687

Reputation: 85

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?

Upvotes: 0

Views: 24663

Answers (3)

Erkan Haspulat
Erkan Haspulat

Reputation: 12562

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.

Upvotes: 5

haki
haki

Reputation: 9759

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.

Upvotes: 1

Rene
Rene

Reputation: 10541

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

Upvotes: 0

Related Questions