🔹 Join the OracleApps88 Telegram Group - Stay up to date with the latest on Oracle EBS R12 and Oracle Cloud/Fusion Applications. 📌 Telegram Group : https://t.me/OracleApps88

💡 Facing issues copying code/scripts or viewing posts? We're here to help!
📬 Contact on Telegram : https://t.me/apps88
📱 Telegram/WhatsApp: +91 905 957 4321
📧 Email Support: OracleApp88@Yahoo.com

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