Bulk
Bind:
Improves the performance of oracle while
manipulating data in Loops in Pl/sql.
It can be implemented with For all Construct
in Pl/SQL.
Example
:
The time taken to
insert, update then delete 10,000 rows using regular FOR..LOOP statements is
approximately 34 seconds on my test server:
CREATE TABLE test1( id NUMBER(10)
primary key, description
VARCHAR2(50));
The
time taken to insert, update then delete 10,000 rows using regular FOR..LOOP
statements is approximately 34 seconds on my test server:
DECLARE
TYPE id_type IS TABLE OF test1.id%TYPE;
TYPE description_type IS
TABLE OF test1.description%TYPE;
t_id id_type := id_type();
t_description description_type :=
description_type();
BEGIN
FOR i IN
1 .. 10000 LOOP
t_id.extend;
t_description.extend;
t_id(t_id.last) := i;
t_description(t_description.last)
:= 'Description: ' || To_Char(i);
END LOOP;
FOR i IN
t_id.first
.. t_id.last
LOOP
INSERT INTO
test1 (id, description)
VALUES (t_id(i),
t_description(i));
END LOOP;
FOR i IN
t_id.first
.. t_id.last
LOOP
UPDATE test1 SET
description = t_description(i)
WHERE id = t_id(i);
END LOOP;
FOR i IN
t_id.first
.. t_id.last
LOOP
DELETE test1 WHERE id = t_id(i);
END LOOP;
Commit;
End;
Using
the FORALL construct to bulk bind the inserts this time is reduced to 18
seconds:
DECLARE
TYPE id_type IS TABLE OF test1.id%TYPE;
TYPE description_type IS
TABLE OF test1.description%TYPE;
t_id id_type := id_type();
t_description description_type :=
description_type();
BEGIN
FOR i IN
1 .. 10000 LOOP
t_id.extend;
t_description.extend;
t_id(t_id.last)
:= i;
t_description(t_description.last)
:= 'Description: ' || To_Char(i);
END LOOP;
FORALL i IN
t_id.first
.. t_id.last
INSERT INTO
test1 (id, description)
VALUES (t_id(i),
t_description(i));
FORALL i IN
t_id.first
.. t_id.last
UPDATE test1 SET
description = t_description(i)
WHERE id = t_id(i);
FORALL i IN
t_id.first
.. t_id.last
DELETE test1 WHERE id = t_id(i);
COMMIT;
END;
/
Using
Bulk Collect : Populating two collections with 10,000 rows using a FOR..LOOP
takes approximately 1.02 seconds:
DECLARE
TYPE id_type IS TABLE OF test1.id%TYPE;
TYPE description_type IS
TABLE OF test1.description%TYPE;
t_id id_type := id_type();
t_description description_type :=
description_type();
CURSOR c_data is
SELECT * FROM test1;
BEGIN
FOR cur_rec IN
c_data LOOP
t_id.extend;
t_description.extend;
t_id(t_id.last) := cur_rec.id;
t_description(t_description.last)
:= cur_rec.description;
END LOOP;
END;
Using
the BULK COLLECT INTO construct reduces this time to approximately 0.01 seconds:
DECLARE
TYPE id_type IS TABLE OF test1.id%TYPE;
TYPE description_type IS
TABLE OF test1.description%TYPE;
t_id id_type;
t_description description_type;
BEGIN
SELECT id,
description
BULK COLLECT
INTO t_id, t_description FROM test1;
END;
/
No comments:
Post a Comment