Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Tuesday, May 29, 2012

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:

Post a Comment

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect