Oracle Illustrated

Feb 21 2010   9:14AM GMT

Performance Tuning – FOR LOOP EXECUTE IMMEDIATE VS FORALL EXECUTE IMMEDIATE



Posted by: Lakshmi Venkatesh
Tags:

Usually FORALL is possible only with DML operations (Insert / Update / Delete / Merge). From Oracle 9i it is possible with EXECUTE IMMEDIATE.

This is just for example purpose only – FORALL is really useful for bulk processing. Though in the below example a direct FORALL – INSERT is possible, have used EXECUTE IMMEDATE to demonstrate its use.

TABLE CREATION SCRIPTS

drop table coll_temp_source

CREATE TABLE coll_temp_source AS SELECT
status, object_id, owner
FROM ALL_OBJECTS where rownum < 201

drop table coll_temp_target

CREATE TABLE coll_temp_target AS SELECT
status, object_id, owner
FROM ALL_OBJECTS where rownum = 5

STEP 1 – USING FORALL

DECLARE
TYPE obj_list IS TABLE OF coll_temp_source%ROWTYPE;
obj_tab obj_list;
TYPE status_list IS TABLE OF coll_temp_source.status%TYPE;
status_tab status_list;
TYPE object_id_list IS TABLE OF coll_temp_source.object_id%TYPE;
object_id_tab object_id_list;
TYPE owner_list IS TABLE OF coll_temp_source.owner%TYPE;
owner_tab owner_list;
BEGIN
SELECT status, object_id, owner BULK COLLECT INTO status_tab, object_id_tab, owner_tab
FROM coll_temp_source WHERE rownum < 11;

FORALL i IN 1 .. status_tab.count
EXECUTE IMMEDIATE
‘INSERT INTO coll_temp_target VALUES (:1, :2 ,:3)’
USING status_tab(i), object_id_tab(i) , owner_tab(i);

END;
/

STEP 2 – USING FOR.. LOOP

DECLARE
TYPE obj_list IS TABLE OF coll_temp_source%ROWTYPE;
obj_tab obj_list;
TYPE status_list IS TABLE OF coll_temp_source.status%TYPE;
status_tab status_list;
TYPE object_id_list IS TABLE OF coll_temp_source.object_id%TYPE;
object_id_tab object_id_list;
TYPE owner_list IS TABLE OF coll_temp_source.owner%TYPE;
owner_tab owner_list;
BEGIN
SELECT status, object_id, owner BULK COLLECT INTO status_tab, object_id_tab, owner_tab
FROM coll_temp_source WHERE rownum @exec.sql

PL/SQL procedure successfully completed.

SQL> select * from coll_temp_target;

STATUS OBJECT_ID OWNER
——- ———- ——————————
VALID 79400 SYS
VALID 69022 SYS
VALID 84837 SYS
VALID 75686 SYS
VALID 84402 SYS
VALID 71636 SYS
VALID 85043 SYS
VALID 85098 SYS
VALID 71312 SYS
VALID 73222 SYS

10 rows selected.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: