Hello There,
Here I am describing a small example of bulk collect and for all clause.This will helpt the execution time of query executed
Before how it work
Thanks for reading the document.
Here I am describing a small example of bulk collect and for all clause.This will helpt the execution time of query executed
Before how it work
A quick glance at the following Code should make one point very clear: This is straightforward code; unfortunately, it takes a lot of time to run - it is "old-fashioned" code, so let's improve it using collections and bulk processing.CREATE OR REPLACE PROCEDURE test_proc IS
BEGIN
FOR x IN (SELECT * FROM all_objects)
LOOP
INSERT INTO t1
(owner, object_name, subobject_name, object_id,
data_object_id, object_type, created, last_ddl_time,
timestamp, status, temporary, generated, secondary)
VALUES
(x.owner, x.object_name, x.subobject_name, x.object_id,
x.data_object_id, x.object_type, x.created,
x.last_ddl_time, x.timestamp, x.status, x.temporary,
x.generated, x.secondary);
END LOOP;
COMMIT;
END test_proc;
/CREATE TABLE t1 AS SELECT * FROM all_objects WHERE 1 = 2;SQL> set timing on;
SQL> exec test_proc;
PL/SQL procedure successfully completed.
Elapsed: 00:00:12.84
SQL> exec test_proc;
PL/SQL procedure successfully completed.
Elapsed: 00:00:15.03
SQL> exec test_proc;
PL/SQL procedure successfully completed.
Elapsed: 00:00:12.54Very slow - do not use it in that way!
USING Bulk Collect:
Converting to collections and bulk processing can increase the volume and complexity of your code. If you need a serious boost in performance, however, that increase is well-justified.Collections, an evolution of PL/SQL tables that allows us to manipulate many variables at once, as a unit. Collections, coupled with two new features introduced with Oracle 8i, BULK_COLLECT and FORALL, can dramatically increase the performance of data manipulation code within PL/SQL.CREATE OR REPLACE PROCEDURE test_proc (p_array_size IN PLS_INTEGER DEFAULT 100)
IS
TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
l_data ARRAY;
CURSOR c IS SELECT * FROM all_objects;
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;
FORALL i IN 1..l_data.COUNT
INSERT INTO t1 VALUES l_data(i);
EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE c;
END test_proc;
/SQL> exec test_proc;
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.34
SQL> exec test_proc;
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.20
SQL> exec test_proc;
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.90
Eliminate CURSOR LOOP at all
You may eliminate the CURSOR Loop at all, the resulting Procedure is compacter and the performance is more or less the same.CREATE OR REPLACE PROCEDURE test_proc
IS
TYPE TObjectTable IS TABLE OF ALL_OBJECTS%ROWTYPE;
ObjectTable$ TObjectTable;
BEGIN
SELECT * BULK COLLECT INTO ObjectTable$
FROM ALL_OBJECTS;
FORALL x in ObjectTable$.First..ObjectTable$.Last
INSERT INTO t1 VALUES ObjectTable$(x) ;
END;
/SQL> exec test_proc;
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.51
SQL> exec test_proc;
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.35
SQL> exec test_proc;
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.46
Thanks for reading the document.
