Oracle BULK COLLECT & FORALL is interest to me so I have colleted some information from internet on this, hope my findings are intrest to you.
PL/SQL engine executes procedural statements but sends SQL statements to the SQL engine, which executes the SQL statements and, in some cases, returns data to the PL/SQL engine.
Too many context switches between the PL/SQL and SQL engines can harm performance. That can happen when a loop executes a separate SQL statement for each element of a collection, specifying the collection element as a bind variable. For example, the following DELETE statement is sent to the SQL engine with each iteration of the FOR loop:
DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70); -- department numbers
BEGIN
…
FOR i IN depts.FIRST..depts.LAST LOOP
DELETE FROM emp WHERE deptno = depts(i);
END LOOP;
…
END;
Using the FORALL Statement
The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an iteration scheme, it is not a FOR loop. Its syntax follows:
FORALL index IN lower_bound..upper_bound
sql_statement;
FORALL does not require a LOOP command.
---
Usually, using of the BULK COLLECT and FORALL statements can drastically improve the performance. For example, I have rewritten a PL/SQL procedure used in a batch job and in result the execution time has been reduced from 12 minutes to 16 seconds – only by using BULK COLLECT and FORALL.
In few words, you must always try to avoid this:
Copyrighted from Steven Feuerstein's book - Oracle PL/SQL Programming, Third Edition
If you are one of these who already are using this feature or you have a PL/SQL procedures that are suffering from slow performance because “row-by-row” processing then may be you will be interested in the following post.
The record types can be defined as based on cursor:
…
CURSOR c_dept_data IS
SELECT dept_id, dept_name
FROM departments
WHERE dept_id in (8,10);
TYPE rt_dept IS
TABLE of c_dept_data%ROWTYPE INDEX BY BINARY_INTEGER;
vrt_dept rt_dept;
…
or based on table:
…
TYPE rt_dept IS
TABLE of departments%ROWTYPE INDEX BY BINARY_INTEGER;
vrt_dept rt_dept;
…
Table DEPARTMENTS have four columns: dept_id,dept_name,dept_mgr_id,dept_description
Lets try to use the BULK COLLECT:
...
SELECT dept_id,dept_name,dept_mgr_id,dept_description BULK COLLECT INTO vrt_dept FROM departments;
...
And to insert the collected data into some temporary table with FORALL:
...
FORALL i IN vrt_dept.FIRST .. vrt_dept.LAST
INSERT INTO departments_tmp VALUES vrt_dept (i);
...
It is working and everything seems okay.
Lets try to do something little bit complicated - to insert only some of the collected columns by changing only the FORALL statement:
...
FORALL i IN vrt_dept.FIRST .. vrt_dept.LAST
INSERT INTO departments_tmp (dept_id,dept_description) VALUES (vrt_dept(i).dept_id,vrt_dept(i).dept_description);
...
In result the following compilation error is returned:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
That is because bulk bind cannot use table of composite types. It works fine with a table of native types, but not with a table of objects or records. The compiler does not support that reference to bulk binds fields. For more information see this Metalink note:
Binding a Collection With FORALL Fails With ORA-01767or PLS-00103
But this is probably the most common case when someone will want to use the BULK COLLECT and FORALL statements (for example - cursor with parameters and joins of three or more tables). The workaround of this problem is to be used tables of native types:
…
TYPE rt_dept_id IS TABLE OF departments.dept_id%TYPE INDEX BY BINARY_INTEGER;
TYPE rt_dept_description IS TABLE OF departments.dept_description%TYPE INDEX BY BINARY_INTEGER;
vrt_dept_id rt_dept_id;
vrt_dept_description rt_dept_description;
…
Then:
…
SELECT dept_id,dept_description BULK COLLECT INTO vrt_dept_id, vrt_dept_description FROM departments;
…
And then:
…
FORALL i IN vrt_dept.FIRST .. vrt_dept.LAST
INSERT INTO departments_tmp (dept_id,dept_description) VALUES (vrt_dept_id(i),vrt_dept_description(i));
…
Now the procedure is compiled and works correctly after testing.
If you are sure about the used types instead declaring the tables by this way:
…
TYPE rt_dept_id IS TABLE OF departments.dept_id%TYPE INDEX BY BINARY_INTEGER;
…
You can define a collection variables in the following way (without defining types, just collection variables):
…
vrt_dept_id dbms_sql.NUMBER_TABLE;
vrt_dept_description dbms_sql.VARCHAR2_TABLE;
…
If there is a possibility the returned record set to be empty then there are few important rules that should be followed.
Lets try some example with a cursor:
…
CURSOR c_dept_data IS
SELECT dept_id, dept_name
FROM departments
WHERE dept_id in (8,10);
TYPE rt_dept_id IS TABLE OF departments.dept_id%TYPE INDEX BY BINARY_INTEGER;
TYPE rt_dept_name IS TABLE OF departments.dept_name%TYPE INDEX BY BINARY_INTEGER;
vrt_dept_id rt_dept_id;
vrt_dept_name rt_dept_name;
…
BEGIN
…
OPEN c_dept_data;
FETCH c_dept_data BULK COLLECT INTO vrt_dept_id,vrt_dept_name;
CLOSE c_dept_data;
FORALL i IN vrt_dept_id.FIRST .. vrt_dept_id.LAST
UPDATE departments_tmp
SET dept_name = vrt_dept_name(i)
WHERE dept_id = vrt_dept_id(i);
…
In this case the procedure is compiled but it returns a runtime error when c_dept_date cursor returns no rows:
ORA-06502: PL/SQL: numeric or value error
This problem can be solved by using of one of the collection methods available: COUNT. The COUNT method returns the number of elements defined in an array:
…
BEGIN
…
OPEN c_dept_data;
LOOP
FETCH c_dept_data BULK COLLECT INTO vrt_dept_id,vrt_dept_name LIMIT 1000;
FORALL i IN 1 .. vrt_dept_id.COUNT
UPDATE departments_tmp
SET dept_name = vrt_dept_name(i)
WHERE dept_id = vrt_dept_id(i);
EXIT WHEN c_dept_data%NOTFOUND;
END LOOP;
CLOSE c_dept_data;
…
In the above case the FIRST and LAST collection methods are replaced by the COUNT collection method, a LOOP block is added plus EXIT condition.
The other workaround is the following:
…
BEGIN
…
OPEN c_dept_data;
FETCH c_dept_data BULK COLLECT INTO vrt_dept_id,vrt_dept_name;
CLOSE c_dept_data;
IF vrt_dept_id.COUNT > 0 THEN
-- do FORALL
ELSE
-- do something else
END IF;
…
Rollback behavior
If one of the bulk DML statements fails, the individual statement is rolled back and previous DML statements within the FORALL series of statements are not rolled back and the FORALL statement stops executing.
For example if you are using FORALL to insert 100 rows and an error appears on 78th row the execution will stop and rows from 78 to 100 will not be inserted indeed. If you want to overcome this behavior and to insert all rows you can use the SAVE EXCEPTIONS clause.
With the SAVE EXCEPTIONS the cursor attribute SQL%BULK_EXCEPTIONS is used. It is an array that records two elements ERROR_INDEX and ERROR_CODE:
...
v_bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (v_bulk_errors, -24381);
…
BEGIN -- outer BEGIN
…
BEGIN –- inner BEGIN clause
OPEN c_dept_data;
LOOP
FETCH c_dept_data BULK COLLECT INTO vrt_dept_id,vrt_dept_name LIMIT 1000;
FORALL i IN 1 .. vrt_dept_id.COUNT
SAVE EXCEPTIONS
UPDATE departments_tmp
SET dept_name = vrt_dept_name(i)
WHERE dept_id = vrt_dept_id(i);
EXIT WHEN c_dept_data%NOTFOUND;
END LOOP;
CLOSE c_dept_data;
EXCEPTION WHEN bulk_errors THEN
FOR i in 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
Dbms_output.put_line(
'An error '|| i ||' was occured ' ||
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
' during update of dept_id: '||
vrt_dept_id(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX)||
'. Oracle error: ' ||
SQLERRM(-1 * SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END; -- end inner BEGIN
…
Be aware of that you can use RETURNING clause with FORALL. This functionality can be used an array with affected rows to be populated and after that to be used for performing other actions with affected data.
Limiting of processed rows
If you want to process an limited count of rows you can use the LIMIT clause. For example, if you need to process a huge table then it is possible to overload your process memory (that will result with an ORA-04030 error) :
...
OPEN c_dept_data;
LOOP
FETCH c_dept_data BULK COLLECT INTO vrt_dept_id,vrt_dept_name LIMIT 1000;
FORALL i IN 1 .. vrt_dept_id.COUNT
UPDATE departments_tmp
SET dept_name = vrt_dept_name(i)
WHERE dept_id = vrt_dept_id(i);
EXIT WHEN c_dept_data%NOTFOUND;
END LOOP;
CLOSE c_dept_data;
...
In this case the collection variables will be populated on every 1000 rows, after that the FORALL will be executed for them and then will be fetched the next 1000. Be aware of that the previous values for the collection are overwritten.
The other workaround to cope with memory problems coming from large PL/SQL tables is to execute the following supplied stored procedure inside your code:
..
dbms_session.free_unused_user_memory;
..
It will free unused memory for your session after large portions of data was processed.
You can use the SQL%BULK_ROWCOUNT attribute, which is available with the FORALL statement. For each processed row in FORALL there is a corresponding row for this attribute. For example, if you want to know did the n-th processed row from FORALL have affected some rows then you must call SQL%BULK_ROWCOUNT(n). If no rows were affected, the n-th row will be zero.
PL/SQL engine executes procedural statements but sends SQL statements to the SQL engine, which executes the SQL statements and, in some cases, returns data to the PL/SQL engine.
DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70); -- department numbers
BEGIN
…
FOR i IN depts.FIRST..depts.LAST LOOP
DELETE FROM emp WHERE deptno = depts(i);
END LOOP;
…
END;
Using the FORALL Statement
The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an iteration scheme, it is not a FOR loop. Its syntax follows:
FORALL index IN lower_bound..upper_bound
sql_statement;
FORALL does not require a LOOP command.
---
Usually, using of the BULK COLLECT and FORALL statements can drastically improve the performance. For example, I have rewritten a PL/SQL procedure used in a batch job and in result the execution time has been reduced from 12 minutes to 16 seconds – only by using BULK COLLECT and FORALL.
In few words, you must always try to avoid this:
Copyrighted from Steven Feuerstein's book - Oracle PL/SQL Programming, Third Edition
If you are one of these who already are using this feature or you have a PL/SQL procedures that are suffering from slow performance because “row-by-row” processing then may be you will be interested in the following post.
The record types can be defined as based on cursor:
…
CURSOR c_dept_data IS
SELECT dept_id, dept_name
FROM departments
WHERE dept_id in (8,10);
TYPE rt_dept IS
TABLE of c_dept_data%ROWTYPE INDEX BY BINARY_INTEGER;
vrt_dept rt_dept;
…
or based on table:
…
TYPE rt_dept IS
TABLE of departments%ROWTYPE INDEX BY BINARY_INTEGER;
vrt_dept rt_dept;
…
Table DEPARTMENTS have four columns: dept_id,dept_name,dept_mgr_id,dept_description
Lets try to use the BULK COLLECT:
...
SELECT dept_id,dept_name,dept_mgr_id,dept_description BULK COLLECT INTO vrt_dept FROM departments;
...
And to insert the collected data into some temporary table with FORALL:
...
FORALL i IN vrt_dept.FIRST .. vrt_dept.LAST
INSERT INTO departments_tmp VALUES vrt_dept (i);
...
It is working and everything seems okay.
Lets try to do something little bit complicated - to insert only some of the collected columns by changing only the FORALL statement:
...
FORALL i IN vrt_dept.FIRST .. vrt_dept.LAST
INSERT INTO departments_tmp (dept_id,dept_description) VALUES (vrt_dept(i).dept_id,vrt_dept(i).dept_description);
...
In result the following compilation error is returned:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
That is because bulk bind cannot use table of composite types. It works fine with a table of native types, but not with a table of objects or records. The compiler does not support that reference to bulk binds fields. For more information see this Metalink note:
Binding a Collection With FORALL Fails With ORA-01767or PLS-00103
But this is probably the most common case when someone will want to use the BULK COLLECT and FORALL statements (for example - cursor with parameters and joins of three or more tables). The workaround of this problem is to be used tables of native types:
…
TYPE rt_dept_id IS TABLE OF departments.dept_id%TYPE INDEX BY BINARY_INTEGER;
TYPE rt_dept_description IS TABLE OF departments.dept_description%TYPE INDEX BY BINARY_INTEGER;
vrt_dept_id rt_dept_id;
vrt_dept_description rt_dept_description;
…
Then:
…
SELECT dept_id,dept_description BULK COLLECT INTO vrt_dept_id, vrt_dept_description FROM departments;
…
And then:
…
FORALL i IN vrt_dept.FIRST .. vrt_dept.LAST
INSERT INTO departments_tmp (dept_id,dept_description) VALUES (vrt_dept_id(i),vrt_dept_description(i));
…
Now the procedure is compiled and works correctly after testing.
If you are sure about the used types instead declaring the tables by this way:
…
TYPE rt_dept_id IS TABLE OF departments.dept_id%TYPE INDEX BY BINARY_INTEGER;
…
You can define a collection variables in the following way (without defining types, just collection variables):
…
vrt_dept_id dbms_sql.NUMBER_TABLE;
vrt_dept_description dbms_sql.VARCHAR2_TABLE;
…
If there is a possibility the returned record set to be empty then there are few important rules that should be followed.
Lets try some example with a cursor:
…
CURSOR c_dept_data IS
SELECT dept_id, dept_name
FROM departments
WHERE dept_id in (8,10);
TYPE rt_dept_id IS TABLE OF departments.dept_id%TYPE INDEX BY BINARY_INTEGER;
TYPE rt_dept_name IS TABLE OF departments.dept_name%TYPE INDEX BY BINARY_INTEGER;
vrt_dept_id rt_dept_id;
vrt_dept_name rt_dept_name;
…
BEGIN
…
OPEN c_dept_data;
FETCH c_dept_data BULK COLLECT INTO vrt_dept_id,vrt_dept_name;
CLOSE c_dept_data;
FORALL i IN vrt_dept_id.FIRST .. vrt_dept_id.LAST
UPDATE departments_tmp
SET dept_name = vrt_dept_name(i)
WHERE dept_id = vrt_dept_id(i);
…
In this case the procedure is compiled but it returns a runtime error when c_dept_date cursor returns no rows:
ORA-06502: PL/SQL: numeric or value error
This problem can be solved by using of one of the collection methods available: COUNT. The COUNT method returns the number of elements defined in an array:
…
BEGIN
…
OPEN c_dept_data;
LOOP
FETCH c_dept_data BULK COLLECT INTO vrt_dept_id,vrt_dept_name LIMIT 1000;
FORALL i IN 1 .. vrt_dept_id.COUNT
UPDATE departments_tmp
SET dept_name = vrt_dept_name(i)
WHERE dept_id = vrt_dept_id(i);
EXIT WHEN c_dept_data%NOTFOUND;
END LOOP;
CLOSE c_dept_data;
…
In the above case the FIRST and LAST collection methods are replaced by the COUNT collection method, a LOOP block is added plus EXIT condition.
The other workaround is the following:
…
BEGIN
…
OPEN c_dept_data;
FETCH c_dept_data BULK COLLECT INTO vrt_dept_id,vrt_dept_name;
CLOSE c_dept_data;
IF vrt_dept_id.COUNT > 0 THEN
-- do FORALL
ELSE
-- do something else
END IF;
…
Rollback behavior
If one of the bulk DML statements fails, the individual statement is rolled back and previous DML statements within the FORALL series of statements are not rolled back and the FORALL statement stops executing.
For example if you are using FORALL to insert 100 rows and an error appears on 78th row the execution will stop and rows from 78 to 100 will not be inserted indeed. If you want to overcome this behavior and to insert all rows you can use the SAVE EXCEPTIONS clause.
With the SAVE EXCEPTIONS the cursor attribute SQL%BULK_EXCEPTIONS is used. It is an array that records two elements ERROR_INDEX and ERROR_CODE:
...
v_bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (v_bulk_errors, -24381);
…
BEGIN -- outer BEGIN
…
BEGIN –- inner BEGIN clause
OPEN c_dept_data;
LOOP
FETCH c_dept_data BULK COLLECT INTO vrt_dept_id,vrt_dept_name LIMIT 1000;
FORALL i IN 1 .. vrt_dept_id.COUNT
SAVE EXCEPTIONS
UPDATE departments_tmp
SET dept_name = vrt_dept_name(i)
WHERE dept_id = vrt_dept_id(i);
EXIT WHEN c_dept_data%NOTFOUND;
END LOOP;
CLOSE c_dept_data;
EXCEPTION WHEN bulk_errors THEN
FOR i in 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
Dbms_output.put_line(
'An error '|| i ||' was occured ' ||
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
' during update of dept_id: '||
vrt_dept_id(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX)||
'. Oracle error: ' ||
SQLERRM(-1 * SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END; -- end inner BEGIN
…
Be aware of that you can use RETURNING clause with FORALL. This functionality can be used an array with affected rows to be populated and after that to be used for performing other actions with affected data.
Limiting of processed rows
If you want to process an limited count of rows you can use the LIMIT clause. For example, if you need to process a huge table then it is possible to overload your process memory (that will result with an ORA-04030 error) :
...
OPEN c_dept_data;
LOOP
FETCH c_dept_data BULK COLLECT INTO vrt_dept_id,vrt_dept_name LIMIT 1000;
FORALL i IN 1 .. vrt_dept_id.COUNT
UPDATE departments_tmp
SET dept_name = vrt_dept_name(i)
WHERE dept_id = vrt_dept_id(i);
EXIT WHEN c_dept_data%NOTFOUND;
END LOOP;
CLOSE c_dept_data;
...
In this case the collection variables will be populated on every 1000 rows, after that the FORALL will be executed for them and then will be fetched the next 1000. Be aware of that the previous values for the collection are overwritten.
The other workaround to cope with memory problems coming from large PL/SQL tables is to execute the following supplied stored procedure inside your code:
..
dbms_session.free_unused_user_memory;
..
It will free unused memory for your session after large portions of data was processed.
You can use the SQL%BULK_ROWCOUNT attribute, which is available with the FORALL statement. For each processed row in FORALL there is a corresponding row for this attribute. For example, if you want to know did the n-th processed row from FORALL have affected some rows then you must call SQL%BULK_ROWCOUNT(n). If no rows were affected, the n-th row will be zero.
Comments
Post a Comment