#---------------------------------------------------------------------------------
# BULK BINDING
#---------------------------------------------------------------------------------
--The assignment of values to PL/SQL variables in SQL statements is called binding.
--The binding of an entire collection at once is referred to as bulk binding.
--Bulk binding is available for select, insert, delete and update statements.
#------------------
# WHY BULK BINDING
#------------------
--If suppose to load 58 millions of rows into a FACT Table in our DATA WAREHOUSE.
--If We wrote a custome procedure which opens a simple cursor and reads all the 58 million
rows from the SOURCE Table and in a loop processes the rows and inserts the records into
a TARGET Table. The logic works fine but it took 20hrs to complete the load.
--To overcome this we will choose BULK BINDING
#----------------
# ADVANTAGE?
#----------------
Prior to Oracle8i, the execution of every SQL statement required a switch
between the PL/SQL and SQL engines, whereas bulk binds use only one context
switch.
Bulk binds improve performance by minimizing the number of context
switches between PL/SQL and SQL engines while they pass an entire collection
of elements (varray, nested tables, index-by table, or host array) as
bind variables back and forth.
#----------------
# ERRORS AND ANSWER FOR ERRORS
#----------------
--We tried with the 600,000 records first and it completed in 1 min and 29 sec
with no problems. We then doubled the no. of rows to 1.2 million and the program
crashed with the following error:
--I think we do have more than enough memory to process the 1 million rows!!
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 16408 bytes
ORA-06512: at "VVA.BULKLOAD", line 66
ORA-06512: at line 1
SGA -- 8.2 GB
PGA -- Aggregate Target - 3GB
-- Current Allocated - 439444KB (439 MB)
-- Maximum allocated - 2695753 KB (2.6 GB)
-- Temp Table Space - 60.9 GB (Total)
-- 20 GB (Available approximately)
ANSWER:
--Use the LIMIT clause, bulk collect say 100 to 1000 rows
-- process them, bulk insert them, get the next 100/1000 rows.
#---------------------
# HOW TO BULK BINDING
#---------------------
(i) For Input collections-- use the FORALL statement
(ii) For Output collections--use BULK COLLECT clause
#---------------------------------------------------------------------------------
# INPUT COLLECTIONS - FORALL
#---------------------------------------------------------------------------------
--Input collections are data passed from the PL/SQL engine to the SQL engine to execute INSERT,
UPDATE, and DELETE statements.
--for all is for bulk input.
--for all run multiple dml statement very efficeintly
--faster than equalent for loop;
--for all only serverside program, not client side.
-- we cannot use the select bulk collect statement in for all statement.
syntax: FORALL index IN lower_bound..upper_bound
sql_statement;
#---------------------------
# EXAMPLE CODE 1
#---------------------------
DECLARE
TYPE NUMLIST IS VARRAY(10) OF NUMBER;
DEPTS NUMLIST := NUMLIST (10,30,70);
BEGIN
FORALL i IN DEPTS.FIRST..DEPTS.LAST
DELETE FROM EMP WHERE DEPT_ID = DEPTS(i);
COMMIT;
END;
#---------------------------
# EXAMPLE CODE 2
#---------------------------
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10,20,50);
BEGIN
FORALL i IN depts.FIRST..depts.LAST
UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
IF SQL%BULK_ROWCOUNT(3) = 0 THEN
DBMS_OUTPUT.PUT_LINE('Its 3!!!');
END IF;
END;
/
#---------------------------
# FORALL ERROR HANDLING
#---------------------------
SQL%BULK_EXCEPTIONS – Collection of records
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX – stores itireration when exception is raised.
SQL%BULK_EXCEPTIONS(i).ERROR_CODE - stores Oracle error code.
#---------------------------------------------------------------------------------
# OUTPUT COLLECTIONS - BULK COLLECT
#---------------------------------------------------------------------------------
--The bulk collect option instructs the SQL engine to bulk bind the output collections
before returning them to the PL/SQL engine.
--This allows us to load data dynamically into collections at one shot for further processing.
--Bulk collect can be used with SELECT INTO, FETCH INTO and RETURNING INTO statements
Syntax:
... BULK COLLECT INTO collection_name[, collection_name] ....
#-------------
# Examples 1
#-------------
DECLARE
TYPE cust_tab IS TABLE OF customer.customer_account_id%TYPE
INDEX BY BINARY_INTEGER;
Custs cust_tab;
BEGIN
SELECT customer_account_id
BULK COLLECT INTO custs
FROM customer
WHERE effective_date BETWEEN
TO_DATE(‘01-Jan-2004’,’DD-MON-RRRR’) AND TRUNC(SYSDATE);
END;;
/
#-------------
# Examples 2
#-------------
DECLARE
TYPE NameTab IS TABLE OF emp.ename%TYPE;
TYPE SalTab IS TABLE OF emp.sal%TYPE;
names NameTab;
sals SalTab;
CURSOR c1 IS SELECT ename, sal FROM emp;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO names, sals;
FOR i IN names.FIRST..names.LAST LOOP
DBMS_OUTPUT.PUT_LINE(names(i) || ' ' || sals(i));
END LOOP;
CLOSE c1;
END;
/
#-----------------------------------
# Bulk used with Select into clause
#-----------------------------------
declare
type emp_details is table of emp.ename%type index by binary_integer;
V emp_details;
begin
select ename bulk collect into V
from emp;
for i in V.first .. V.last
loop
dbms_output.put_line(V(i));
end loop;
end;
#-------------
# Bulk used in Cursors
#-------------
declare
cursor cf is select * from emp;
type emp_tab is table of emp%rowtype index by binary_integer;
V emp_tab;
v_limit natural := 10;
begin
open cf;
fetch cf bulk collect into V limit v_limit;
for j in V.first .. V.last
loop
dbms_output.put_line(V(j).ename);
end loop;
end;
#-------------
# Bulk Insert
#-------------
Create table BI (a number check(a between 5 and 45));
declare
type no_list is table of number index by binary_integer;
v no_list;
bulk_errors exception;
pragma exception_init ( bulk_errors, -24381 );
begin
for i in 5..50
loop
v(i) := i;
end loop;
forall j in V.first .. V.last save exceptions
insert into bi values (V(j));
dbms_output.put_line('Records inserted');
exception
when bulk_errors then
for j in 1..sql%bulk_exceptions.count
loop
Dbms_Output.Put_Line ( 'Error from element #' ||
To_Char(sql%bulk_exceptions(j).error_index) || ': ' ||
Sqlerrm(-sql%bulk_exceptions(j).error_code) );
end loop;
end;
#-------------
# Bulk Delete
#-------------
declare
type emp_tab is table of emp%rowtype index by binary_integer;
V emp_tab;
begin
delete from emp
returning empno,ename,job,mgr,hiredate,sal,comm,deptno bulk collect into V;
for i in V.first .. v.last
loop
dbms_output.put_line(V(i).ename);
end loop;
end;
#---------------------------------------------------------------------------------
# FORALL VS BULKCOLLECT
#---------------------------------------------------------------------------------
In this example, 5000 part numbers and names are loaded into index-by
tables. Then, all table elements are inserted into a database table
twice. First, they are inserted using a FOR loop, which completes in
8 seconds. Then, they are inserted using a FORALL statement, which
completes in only 0 seconds.
SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));
DECLARE
TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
pnums NumTab;
pnames NameTab;
t1 CHAR(5);
t2 CHAR(5);
t3 CHAR(5);
PROCEDURE get_time(t OUT NUMBER) IS
BEGIN
SELECT TO_CHAR(SYSDATE,'SSSSS') INTO t FROM dual;
END;
BEGIN
FOR j IN 1..5000 LOOP -- load index-by tables
pnums(j) := j;
pnames(j) := 'Part No. ' || TO_CHAR(j);
END LOOP;
get_time(t1);
FOR i IN 1..5000 LOOP -- use FOR loop
INSERT INTO parts VALUES (pnums(i), pnames(i));
END LOOP;
get_time(t2);
FORALL i IN 1..5000 --use FORALL statement
INSERT INTO parts VALUES (pnums(i), pnames(i));
get_time(t3);
DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');
DBMS_OUTPUT.PUT_LINE('---------------------');
DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR(t2 - t1));
DBMS_OUTPUT.PUT_LINE('FORALL: ' || TO_CHAR(t3 - t2));
END;
/
SQL> @bulk.sql
Execution Time (secs)
---------------------
FOR loop: 8
FORALL: 0
#---------------------------------------------------------------------------------
# FOR_UPDATE
#---------------------------------------------------------------------------------
-- We cursor to update/delete the current row
--cursor must be for update.
--cursor must be open.
--if cursor is not open, this clause causes error.
--for update (of column.name)(no wait);
--if lock rows before you update or delete rows(nowait) returns an oracle error if the rows are locked by another session
--it tells oracle not to wait if requested rows have been locked by another user.
DECLARE
CURSOR XX_CUR IS SELECT ENAME, SAL FROM EMP;
FOR UPDATE OF SAL NOWAIT;
BEGIN
FOR i IN XX_CUR
LOOP
IF XX_CUR.SAL<5000 THEN
UPDATE EMP SET SAL =i.SAL*2
WHERE CURRENT OF XX_CUR;
END IF;
END LOOP;
END;
DECLARE
TYPE typ_all_objects IS TABLE OF all_objects%ROWTYPE;
tab_all_objects typ_all_objects;
CURSOR bulk_collect_limit is
SELECT * FROM all_objects;
BEGIN
OPEN bulk_collect_limit;
LOOP
FETCH bulk_collect_limit BULK COLLECT INTO tab_all_objects LIMIT 20000 ;
EXIT WHEN tab_all_objects.COUNT = 0;
-- processing codes
dbms_output.put_line(lpad(tab_all_objects.COUNT,5,' ') || ' Rows');
END LOOP;
CLOSE bulk_collect_limit;
END;
DECLARE
TYPE typ_all_objects IS TABLE OF all_objects%ROWTYPE;
tab_all_objects typ_all_objects := typ_all_objects();
l_start number;
BEGIN
l_start := dbms_utility.get_time;
FOR cur_rec IN (SELECT * FROM all_objects) LOOP
tab_all_objects.extend;
tab_all_objects(tab_all_objects.last) := cur_rec;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Simple Cursor took '|| (dbms_utility.get_time - l_start) ||
' to process '|| tab_all_objects.count||' rows; ');
l_start := dbms_utility.get_time;
SELECT *
BULK COLLECT INTO
tab_all_objects
FROM all_objects;
DBMS_OUTPUT.PUT_LINE('Bulk Collect took '|| (dbms_utility.get_time - l_start) ||
' to process '|| tab_all_objects.count||' rows; ');
END;
# BULK BINDING
#---------------------------------------------------------------------------------
--The assignment of values to PL/SQL variables in SQL statements is called binding.
--The binding of an entire collection at once is referred to as bulk binding.
--Bulk binding is available for select, insert, delete and update statements.
#------------------
# WHY BULK BINDING
#------------------
--If suppose to load 58 millions of rows into a FACT Table in our DATA WAREHOUSE.
--If We wrote a custome procedure which opens a simple cursor and reads all the 58 million
rows from the SOURCE Table and in a loop processes the rows and inserts the records into
a TARGET Table. The logic works fine but it took 20hrs to complete the load.
--To overcome this we will choose BULK BINDING
#----------------
# ADVANTAGE?
#----------------
Prior to Oracle8i, the execution of every SQL statement required a switch
between the PL/SQL and SQL engines, whereas bulk binds use only one context
switch.
Bulk binds improve performance by minimizing the number of context
switches between PL/SQL and SQL engines while they pass an entire collection
of elements (varray, nested tables, index-by table, or host array) as
bind variables back and forth.
#----------------
# ERRORS AND ANSWER FOR ERRORS
#----------------
--We tried with the 600,000 records first and it completed in 1 min and 29 sec
with no problems. We then doubled the no. of rows to 1.2 million and the program
crashed with the following error:
--I think we do have more than enough memory to process the 1 million rows!!
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 16408 bytes
ORA-06512: at "VVA.BULKLOAD", line 66
ORA-06512: at line 1
SGA -- 8.2 GB
PGA -- Aggregate Target - 3GB
-- Current Allocated - 439444KB (439 MB)
-- Maximum allocated - 2695753 KB (2.6 GB)
-- Temp Table Space - 60.9 GB (Total)
-- 20 GB (Available approximately)
ANSWER:
--Use the LIMIT clause, bulk collect say 100 to 1000 rows
-- process them, bulk insert them, get the next 100/1000 rows.
#---------------------
# HOW TO BULK BINDING
#---------------------
(i) For Input collections-- use the FORALL statement
(ii) For Output collections--use BULK COLLECT clause
#---------------------------------------------------------------------------------
# INPUT COLLECTIONS - FORALL
#---------------------------------------------------------------------------------
--Input collections are data passed from the PL/SQL engine to the SQL engine to execute INSERT,
UPDATE, and DELETE statements.
--for all is for bulk input.
--for all run multiple dml statement very efficeintly
--faster than equalent for loop;
--for all only serverside program, not client side.
-- we cannot use the select bulk collect statement in for all statement.
syntax: FORALL index IN lower_bound..upper_bound
sql_statement;
#---------------------------
# EXAMPLE CODE 1
#---------------------------
DECLARE
TYPE NUMLIST IS VARRAY(10) OF NUMBER;
DEPTS NUMLIST := NUMLIST (10,30,70);
BEGIN
FORALL i IN DEPTS.FIRST..DEPTS.LAST
DELETE FROM EMP WHERE DEPT_ID = DEPTS(i);
COMMIT;
END;
#---------------------------
# EXAMPLE CODE 2
#---------------------------
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10,20,50);
BEGIN
FORALL i IN depts.FIRST..depts.LAST
UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
IF SQL%BULK_ROWCOUNT(3) = 0 THEN
DBMS_OUTPUT.PUT_LINE('Its 3!!!');
END IF;
END;
/
#---------------------------
# FORALL ERROR HANDLING
#---------------------------
SQL%BULK_EXCEPTIONS – Collection of records
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX – stores itireration when exception is raised.
SQL%BULK_EXCEPTIONS(i).ERROR_CODE - stores Oracle error code.
#---------------------------------------------------------------------------------
# OUTPUT COLLECTIONS - BULK COLLECT
#---------------------------------------------------------------------------------
--The bulk collect option instructs the SQL engine to bulk bind the output collections
before returning them to the PL/SQL engine.
--This allows us to load data dynamically into collections at one shot for further processing.
--Bulk collect can be used with SELECT INTO, FETCH INTO and RETURNING INTO statements
Syntax:
... BULK COLLECT INTO collection_name[, collection_name] ....
#-------------
# Examples 1
#-------------
DECLARE
TYPE cust_tab IS TABLE OF customer.customer_account_id%TYPE
INDEX BY BINARY_INTEGER;
Custs cust_tab;
BEGIN
SELECT customer_account_id
BULK COLLECT INTO custs
FROM customer
WHERE effective_date BETWEEN
TO_DATE(‘01-Jan-2004’,’DD-MON-RRRR’) AND TRUNC(SYSDATE);
END;;
/
#-------------
# Examples 2
#-------------
DECLARE
TYPE NameTab IS TABLE OF emp.ename%TYPE;
TYPE SalTab IS TABLE OF emp.sal%TYPE;
names NameTab;
sals SalTab;
CURSOR c1 IS SELECT ename, sal FROM emp;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO names, sals;
FOR i IN names.FIRST..names.LAST LOOP
DBMS_OUTPUT.PUT_LINE(names(i) || ' ' || sals(i));
END LOOP;
CLOSE c1;
END;
/
#-----------------------------------
# Bulk used with Select into clause
#-----------------------------------
declare
type emp_details is table of emp.ename%type index by binary_integer;
V emp_details;
begin
select ename bulk collect into V
from emp;
for i in V.first .. V.last
loop
dbms_output.put_line(V(i));
end loop;
end;
#-------------
# Bulk used in Cursors
#-------------
declare
cursor cf is select * from emp;
type emp_tab is table of emp%rowtype index by binary_integer;
V emp_tab;
v_limit natural := 10;
begin
open cf;
fetch cf bulk collect into V limit v_limit;
for j in V.first .. V.last
loop
dbms_output.put_line(V(j).ename);
end loop;
end;
#-------------
# Bulk Insert
#-------------
Create table BI (a number check(a between 5 and 45));
declare
type no_list is table of number index by binary_integer;
v no_list;
bulk_errors exception;
pragma exception_init ( bulk_errors, -24381 );
begin
for i in 5..50
loop
v(i) := i;
end loop;
forall j in V.first .. V.last save exceptions
insert into bi values (V(j));
dbms_output.put_line('Records inserted');
exception
when bulk_errors then
for j in 1..sql%bulk_exceptions.count
loop
Dbms_Output.Put_Line ( 'Error from element #' ||
To_Char(sql%bulk_exceptions(j).error_index) || ': ' ||
Sqlerrm(-sql%bulk_exceptions(j).error_code) );
end loop;
end;
#-------------
# Bulk Delete
#-------------
declare
type emp_tab is table of emp%rowtype index by binary_integer;
V emp_tab;
begin
delete from emp
returning empno,ename,job,mgr,hiredate,sal,comm,deptno bulk collect into V;
for i in V.first .. v.last
loop
dbms_output.put_line(V(i).ename);
end loop;
end;
#---------------------------------------------------------------------------------
# FORALL VS BULKCOLLECT
#---------------------------------------------------------------------------------
In this example, 5000 part numbers and names are loaded into index-by
tables. Then, all table elements are inserted into a database table
twice. First, they are inserted using a FOR loop, which completes in
8 seconds. Then, they are inserted using a FORALL statement, which
completes in only 0 seconds.
SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));
DECLARE
TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
pnums NumTab;
pnames NameTab;
t1 CHAR(5);
t2 CHAR(5);
t3 CHAR(5);
PROCEDURE get_time(t OUT NUMBER) IS
BEGIN
SELECT TO_CHAR(SYSDATE,'SSSSS') INTO t FROM dual;
END;
BEGIN
FOR j IN 1..5000 LOOP -- load index-by tables
pnums(j) := j;
pnames(j) := 'Part No. ' || TO_CHAR(j);
END LOOP;
get_time(t1);
FOR i IN 1..5000 LOOP -- use FOR loop
INSERT INTO parts VALUES (pnums(i), pnames(i));
END LOOP;
get_time(t2);
FORALL i IN 1..5000 --use FORALL statement
INSERT INTO parts VALUES (pnums(i), pnames(i));
get_time(t3);
DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');
DBMS_OUTPUT.PUT_LINE('---------------------');
DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR(t2 - t1));
DBMS_OUTPUT.PUT_LINE('FORALL: ' || TO_CHAR(t3 - t2));
END;
/
SQL> @bulk.sql
Execution Time (secs)
---------------------
FOR loop: 8
FORALL: 0
#---------------------------------------------------------------------------------
# FOR_UPDATE
#---------------------------------------------------------------------------------
-- We cursor to update/delete the current row
--cursor must be for update.
--cursor must be open.
--if cursor is not open, this clause causes error.
--for update (of column.name)(no wait);
--if lock rows before you update or delete rows(nowait) returns an oracle error if the rows are locked by another session
--it tells oracle not to wait if requested rows have been locked by another user.
DECLARE
CURSOR XX_CUR IS SELECT ENAME, SAL FROM EMP;
FOR UPDATE OF SAL NOWAIT;
BEGIN
FOR i IN XX_CUR
LOOP
IF XX_CUR.SAL<5000 THEN
UPDATE EMP SET SAL =i.SAL*2
WHERE CURRENT OF XX_CUR;
END IF;
END LOOP;
END;
DECLARE
TYPE typ_all_objects IS TABLE OF all_objects%ROWTYPE;
tab_all_objects typ_all_objects;
CURSOR bulk_collect_limit is
SELECT * FROM all_objects;
BEGIN
OPEN bulk_collect_limit;
LOOP
FETCH bulk_collect_limit BULK COLLECT INTO tab_all_objects LIMIT 20000 ;
EXIT WHEN tab_all_objects.COUNT = 0;
-- processing codes
dbms_output.put_line(lpad(tab_all_objects.COUNT,5,' ') || ' Rows');
END LOOP;
CLOSE bulk_collect_limit;
END;
DECLARE
TYPE typ_all_objects IS TABLE OF all_objects%ROWTYPE;
tab_all_objects typ_all_objects := typ_all_objects();
l_start number;
BEGIN
l_start := dbms_utility.get_time;
FOR cur_rec IN (SELECT * FROM all_objects) LOOP
tab_all_objects.extend;
tab_all_objects(tab_all_objects.last) := cur_rec;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Simple Cursor took '|| (dbms_utility.get_time - l_start) ||
' to process '|| tab_all_objects.count||' rows; ');
l_start := dbms_utility.get_time;
SELECT *
BULK COLLECT INTO
tab_all_objects
FROM all_objects;
DBMS_OUTPUT.PUT_LINE('Bulk Collect took '|| (dbms_utility.get_time - l_start) ||
' to process '|| tab_all_objects.count||' rows; ');
END;