add

About Me

My photo
Oracle Apps - Techno Functional consultant

Tuesday, May 29

FORALL loop performance oracle pl 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 NUMBER(9);
t2 NUMBER(9);
t3 NUMBER(9);
BEGIN
FOR j IN 1..5000 LOOP -- load index-by tables
pnums(j) := j;
pnames(j) := 'Part No. ' TO_CHAR(j);
END LOOP;

t1 := dbms_utility.get_time;
FOR i IN 1..5000 LOOP -- use FOR loop
INSERT INTO parts VALUES (pnums(i), pnames(i));
END LOOP;
t2 := dbms_utility.get_time;

FORALL i IN 1..5000 -- use FORALL statement
INSERT INTO parts VALUES (pnums(i), pnames(i));
t3 := dbms_utility.get_time;

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;

No comments: