Examples to Test the BULK Collect:
Example-1:
Test to compare the performance of populating plsql table type
variable using for loop cursor and bulk collect.
declare
type bulk_test_mah_tab is table of bulk_test_mah%rowtype;
v_bulk_test_mah_tab bulk_test_mah_tab := bulk_test_mah_tab();
v_start_time pls_integer;
begin
v_start_time :=dbms_utility.get_time;
for i in (select * from bulk_test_mah) loop
v_bulk_test_mah_tab.extend; -- to increment one
cell to hold new values
v_bulk_test_mah_tab (v_bulk_test_mah_tab.last) := i;
end loop;
dbms_output.put_line ('Time taken by for loop -> ' || (dbms_utility.get_time - v_start_time ) ||' for rows -'||
v_bulk_test_mah_tab.count);
rollback;
v_start_time :=dbms_utility.get_time;
select * bulk collect into v_bulk_test_mah_tab
from bulk_test_mah;
dbms_output.put_line ('Time taken by bulk
collect ->' || (dbms_utility.get_time - v_start_time) || ' for rows -' ||
v_bulk_test_mah_tab.count);
exception
when others then
dbms_output.put_line ('error -' || sqlerrm );
end;
output1:
Time taken by for loop -> 30 for rows -57958
Time taken by bulk collect ->10
for rows -57958
Conclusion -1: It is proven with above example-1 that populating plsql table
type with bulk collect is much more efficient than populating it using for
loop.
------------------------------------------------------------------------------------------------
Example-2:
Test to compare the time taken by select bulk collect into and for
all statement with DML associated with it.
declare
st_time pls_integer ;
type ord_mah_tab is table of ord_mah.order_no%type;
v_ord_mah_tab ord_mah_tab;
begin
st_time := dbms_utility.get_time;
dbms_output.put_line ('st_time 1 is -> ' || st_time );
select order_no bulk collect into v_ord_mah_tab from ord_mah;
dbms_output.put_line ('Time taken for
select with bulk collect ->' || (dbms_utility.get_time - st_time ) );
st_time := dbms_utility.get_time;
dbms_output.put_line ('st_time 2 is -> ' || st_time );
forall i in v_ord_mah_tab.first..v_ord_mah_tab.count
update ord_mah set customer_po_no = substr(customer_po_no,1,7) where order_no = v_ord_mah_tab(i);
dbms_output.put_line ('Time taken for exeuction using
forall ->' || (dbms_utility.get_time - st_time ) );
rollback;
exception
when others then
dbms_output.put_line ( dbms_utility.format_error_stack
|| '-' || dbms_utility.format_error_backtrace
);
end;
output2:
st_time 1 is -> 431409877
Time taken for select with bulk collect ->4
st_time 2 is -> 431409881
Time taken for exeuction using
forall ->25009
Conclusion -2:
From the above test, it is proven that select bulk collect into is
executed in no time and the over head is associated with update statement in
forall.
------------------------------------------------------------------------------------------------
Example-3:
Test to compare DML using direct update, using for loop and using
FORALL statements
declare
st_time pls_integer ;
type ord_mah_tab is table of ord_mah.order_no%type;
v_ord_mah_tab ord_mah_tab;
begin
st_time := dbms_utility.get_time;
dbms_output.put_line ('st_time 1 is -> ' || st_time );
update ord_mah set customer_po_no = substr(customer_po_no,1,7);
dbms_output.put_line ('Time taken for exeuction using
direct update ->' || (dbms_utility.get_time - st_time ) );
rollback;
st_time := dbms_utility.get_time;
dbms_output.put_line ('st_time 2 is -> ' || st_time );
for i in (select order_no from ord_mah) loop
update ord_mah set customer_po_no = substr(customer_po_no,1,7) where order_no = i.order_no;
end loop;
dbms_output.put_line ('Time taken for exeuction using for
loop ->' || (dbms_utility.get_time - st_time ) );
rollback;
st_time := dbms_utility.get_time;
dbms_output.put_line ('st_time 3 is -> ' || st_time );
select order_no bulk collect into v_ord_mah_tab from ord_mah;
forall i in v_ord_mah_tab.first..v_ord_mah_tab.count
update ord_mah set customer_po_no = substr(customer_po_no,1,7) where order_no = v_ord_mah_tab(i);
dbms_output.put_line ('Time taken for exeuction using
forall ->' || (dbms_utility.get_time - st_time ) );
rollback;
exception
when others then
dbms_output.put_line ( dbms_utility.format_error_stack
|| '-' || dbms_utility.format_error_backtrace
);
end;
output3:
st_time 1 is -> 431311406
Time taken for exeuction using direct update
->137
st_time 2 is -> 431311654
Time taken for exeuction using for loop ->22853
st_time 3 is -> 431334572
Time taken for exeuction using
forall ->23074
------------------------------------------------------------------------------------------------
LIMIT Clause:
Remember that collections are held in memory, so doing a bulk
collect from a large query could cause a considerable performance problem. In
actual fact you would rarely do a straight bulk collect in this manner. Instead
you would limit the rows returned using the
LIMIT clause and move through the data
processing smaller chunks. This gives you the benefits of bulk binds, without
hogging all the server memory.
Example-4:
Test to limit the bulk collect into given number of rows
declare
type bulk_test_mah_tab is table of bulk_test_mah%rowtype;
v_bulk_test_mah_tab bulk_test_mah_tab := bulk_test_mah_tab();
v_start_time pls_integer;
cursor c1 is select * from bulk_test_mah;
begin
v_start_time :=dbms_utility.get_time;
open c1;
loop
fetch c1 bulk collect into v_bulk_test_mah_tab limit 10000;
--exit when c1%notfound;
exit when v_bulk_test_mah_tab.count = 0;
dbms_output.put_line ('No of rows in table
type ->' ||
v_bulk_test_mah_tab.count );
end loop;
close c1;
exception
when others then
dbms_output.put_line ('error -' || sqlerrm );
end;
Output-4:
No of rows in table type ->10000
No of rows in table type ->10000
No of rows in table type ->10000
No of rows in table type ->10000
No of rows in table type ->10000
No of rows in table type ->7958
-----------------------------------------------------------------------------------------------
Example-5:
Test to track the errors using SAVE EXCEPTIONS while performing DML using FORALL
declare
type ord_mah_tab is table of ord_mah.order_no%type;
v_ord_mah_tab ord_mah_tab;
begin
select order_no bulk collect into v_ord_mah_tab from ord_mah;
forall i in v_ord_mah_tab.first..v_ord_mah_tab.count save exceptions
update ord_mah set customer_po_no = customer_po_no ||'X' where order_no = v_ord_mah_tab(i);
exception
when others then
if sqlcode= -24381 then
for indx in 1..SQL%BULK_EXCEPTIONS.COUNT loop
dbms_output.put_line
( SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX || '- ORA-' || SQL%BULK_EXCEPTIONS(indx).ERROR_CODE );
end loop;
else
dbms_output.put_line ( dbms_utility.format_error_stack
|| '- ORA-' || dbms_utility.format_error_backtrace
);
end if;
end;
Output-5: The below are the failed rows while performing
update
1- ORA-12899
2- ORA-12899
3- ORA-12899
9199- ORA-12899
9313- ORA-12899
14968- ORA-12899
-----------------------------------------------------------------------------------------------
Example-5:
Test FORALL with sparse collections by using VALUES OF and/or
INDICES OF clauses
Use INDICES OF for Nested table type collection and VALUES OF for
associative array type collection
In a FORALL statement, the VALUES OF clause allows the values of
one collection to be used as a index pointers to another collection.
DECLARE
TYPE t_tab1 IS TABLE OF tab1%ROWTYPE;
TYPE t_tab2 IS TABLE OF BINARY_INTEGER;
l_tab1
t_tab1 := t_tab1();
l_tab2
t_tab2 := t_tab2();
BEGIN
FOR i IN 1 .. 1000 LOOP
l_tab1.extend;
l_tab1(l_tab1.last).id := i;
IF MOD(i, 100) = 0 THEN
l_tab2.extend;
l_tab2(l_tab2.last) := i;
END IF;
END LOOP;
l_tab1.delete(301);
l_tab1.delete(601);
l_tab1.delete(901);
-- This would fail due to sparse
collection. --FORALL i IN l_tab.first ..
l_tab.last
--
INSERT INTO tab1 VALUES l_tab(i);
-- This works fine with sparse
collections.
FORALL i IN INDICES OF l_tab1
INSERT INTO tab1 VALUES l_tab1(i);
-- This works fine for collections of
indexes -- pointing to elements of another
collection.
FORALL i IN VALUES OF l_tab2
INSERT INTO tab1 VALUES l_tab1(i);
END;
PLS-00436 Restriction in FORALL Statements Removed in 11G
The PLS-00436 restriction has been removed, which means you can
now reference the individual elements of a collection within the SET and WHERE clauses of a DML statement in a FORALL
construct.
If we execute the following test in ORACLE 10G, we will get the
following error
PLS-00436: implementation restriction:
cannot reference fields of BULK In-BIND table of records
Example-6:
CREATE TABLE forall_test (
id NUMBER,
description VARCHAR2(50)
);
INSERT INTO forall_test VALUES (1, 'ONE');
INSERT INTO forall_test VALUES (2, 'TWO');
INSERT INTO forall_test VALUES (3, 'THREE');
INSERT INTO forall_test VALUES (4, 'FOUR');
INSERT INTO forall_test VALUES (5, 'FIVE');
COMMIT;
DECLARE
TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;
l_tab t_forall_test_tab;
BEGIN
--
Retrieve the existing data into a collection.
SELECT *
BULK COLLECT INTO l_tab
FROM forall_test;
-- Alter
the data in the collection.
FOR i IN l_tab.first .. l_tab.last LOOP
l_tab(i).description := 'Description for ' || i;
END LOOP;
--
Update the table using the collection.
FORALL i IN l_tab.first .. l_tab.last
UPDATE forall_test
SET description = l_tab(i).description
WHERE id = l_tab(i).id;
COMMIT;
END;
Output:
-----------------------------------------------------------------------------------------------
Example-7: Testing BULK COLLECT with RETURNING clause
declare
type ord_mah_tab is table of ord_mah.order_no%type;
v_ord_mah_tab ord_mah_tab;
begin
delete from ord_mah returning order_no bulk collect into v_ord_mah_tab;
rollback;
dbms_output.put_line ('No of Rows going to be deleted ->
' || v_ord_mah_tab.count );
end;
Output-7:
No of Rows going to be deleted ->
17960
Example-8: Testing BULK COLLECT with FORALL, RETURNING
declare
type ord_mah_ord is table of ord_mah.order_no%type;
v_ord_mah_ord ord_mah_ord;
v_ord_mah_tab ord_mah_ord;
begin
select order_no bulk collect into v_ord_mah_ord from ord_mah where rownum < 101;
forall i in 1..v_ord_mah_ord.count
delete from ord_mah where order_no = v_ord_mah_ord(i) returning order_no bulk collect into v_ord_mah_tab;
rollback;
dbms_output.put_line ('No of Rows in v_ord_mah_ord -> ' || v_ord_mah_ord.count );
dbms_output.put_line ('No of Rows in v_ord_mah_tab -> ' || v_ord_mah_tab.count );
end;
output-8:
No of Rows in v_ord_mah_ord -> 100
No of Rows in v_ord_mah_tab -> 100
Example-9: Testing SQL%BULK_ROWCOUNT
declare
type ord_mah_tab is table of ord_mah.customer_po_no%type;
v_ord_mah_tab ord_mah_tab ;
begin
select distinct customer_po_no bulk collect into v_ord_mah_tab from ord_mah
where customer_po_no in ('621000441XX','621000442XX','253P14XX','M7312210BXX','FG04/TOOLCRIBXX','PLANNEDXX','AC449AXX','5500452851-0001188335XX','5500452851-0001180273XX');
dbms_output.put_line ('no of rows ->' || v_ord_mah_tab.count );
forall idx in v_ord_mah_tab.first..v_ord_mah_tab.last
delete from ord_mah where customer_po_no = v_ord_mah_tab(idx);
for idx in 1..v_ord_mah_tab.count loop
dbms_output.put_line ('No of orders
assosciated with customer po no ->' || v_ord_mah_tab(idx) ||' --- '|| SQL%BULK_ROWCOUNT(idx) );
end loop;
rollback;
exception
when others then
dbms_output.put_line ('Error-' || sqlerrm );
end;
Output-9:
no of rows ->9
No of orders associated with customer po no
->621000441XX --- 13
No of orders associated with customer po no
->621000442XX --- 11
No of orders associated with customer po no
->253P14XX --- 2
No of orders associated with customer po no
->M7312210BXX --- 2
No of orders associated with customer po no
->FG04/TOOLCRIBXX --- 2
No of orders associated with customer po no
->PLANNEDXX --- 2
No of orders associated with customer po no
->AC449AXX --- 13
No of orders associated with customer po no
->5500452851-0001188335XX --- 3
No of orders associated with customer
po no ->5500452851-0001180273XX --- 5
Note:
·
In Oracle 10g and above, the optimizing
PL/SQL compiler rewrites conventional cursor for loops to use a
BULK COLLECT with a LIMIT 100, so
code that previously didn't take advantage of bulk binds may now run faster.
·
The restriction on accessing individual
columns of the collection with a
FORALL has
been removed in Oracle 11g (here).
·
For bulk updates and deletes the timing
points remain unchanged. Each row in the collection triggers a before
statement, before row, after row and after statement timing point. For bulk
inserts, the statement level triggers only fire at the start and the end of the
the whole bulk operation, rather than for each row of the collection
Such an ideal piece of blog.Thanks for sharing
ReplyDeleteOracle SOA Online Training