Bulk Collect In Oracle PL/SQL
-------------------------------
-------------------------------
Using Oracle BULK COLLECT technique enables the PL/SQL engine
to collect many rows at once and place them in a collection of array.
A collection is an ordered group of elements, all of the
same type. It is a general concept that encompasses lists, arrays, and other
familiar datatypes. Each element has a unique subscript that determines its
position in the collection.
You should have heard that BULK COLLECT together with FORALL
can help a PL/SQL to perform better in terms of average execution time.
The keyword FORALL instructs the PL/SQL engine to
bulk-bind input collections before sending them to the SQL engine.
FORALL does not require a LOOP command.
BULK COLLECT and FORALL statements can drastically improve
the performance. For example, I have rewritten a PL/SQL procedure used in
a batch job and in result the execution time has been reduced from 40 minutes
to 30 seconds only by using BULK COLLECT and FORALL.
DECLARE
-- Begin Cursor Definition
CURSOR bulk_table_select IS
SELECT TAB.object_id,
TAB.apps_id,
TAB.batch_id
FROM table_select TAB_SELECT;
TYPE object_id_tab IS TABLE OF table_select.object_id%TYPE
INDEX BY BINARY_INTEGER;
TYPE apps_id_tab IS TABLE OF table_select.apps_id%TYPE INDEX
BY BINARY_INTEGER;
TYPE batch_id_tab IS TABLE OF table_select.batch_id%TYPE
INDEX BY BINARY_INTEGER;
lt_object_id object_id_tab;
lt_apps_id apps_id_tab;
lt_batch_id batch_id_tab;
BEGIN
-- Begin Bulk Select & Delete
OPEN bulk_table_select;
LOOP
FETCH bulk_table_select BULK COLLECT INTO
lt_object_id,lt_apps_id,lt_batch_id
LIMIT 5000;
EXIT WHEN lt_batch_id.COUNT = 0;
FORALL i IN lt_batch_id.FIRST..lt_batch_id.LAST
DELETE FROM table_delete TAB_DELETE
WHERE batch_id = lt_batch_id(i)
AND apps_id = lt_apps_id(i);
END LOOP;
CLOSE bulk_table_select;
commit;
END;
Another Example with runtime limit clause parameter:
CREATE OR REPLACE PROCEDURE update_rows_with_limit
(p_commit_row_count NUMBER)
IS
stat VARCHAR2 (32000);
TYPE ref_cur IS REF CURSOR;
c ref_cur;
TYPE myarray IS TABLE OF VARCHAR2 (500)
INDEX BY BINARY_INTEGER;
rid myarray;
tot_rows NUMBER := 0;
BEGIN
stat := 'select rowid rid from emp e where sal<3000';
OPEN c FOR stat;
LOOP
FETCH c
BULK COLLECT INTO rid LIMIT p_commit_row_count;
IF rid.FIRST > 0
THEN
FORALL i IN rid.FIRST .. rid.LAST
EXECUTE IMMEDIATE 'update emp set sal=sal+1000 where
rowid=:rno'
USING rid (i);
COMMIT;
END IF;
tot_rows := tot_rows + rid.LAST;
EXIT WHEN c%NOTFOUND;
END LOOP;
END;
ORA-04030: out of process memory when trying to allocate
16396 bytes (koh-kghu call ,pl/sql vc2)
OPEN c FOR stat;
LOOP
FETCH c
BULK COLLECT INTO q_alias_name LIMIT 1000;
IF q_alias_name.FIRST > 0
THEN
FORALL i IN q_alias_name.FIRST .. q_alias_name.LAST
INSERT INTO EP_COLUMN_NAMES
(ep_pdsu_id, entity, column_alias_name,
when_created, who_created, when_updated, who_updated
)
VALUES (pdsu_id, p_entity, q_alias_name (i),
SYSDATE, p_userid, SYSDATE, p_userid
);
EXIT WHEN c%NOTFOUND;
END IF;
END LOOP;
Here q_alias_name.FIRST > 0 means if stat return values
then above code works fine..otherwise it will go into infinite loop. so u need
to put EXIT WHEN c%NOTFOUND; at outside of the end if like below.
EXIT WHEN c%NOTFOUND;--(wrong)
END IF;
EXIT WHEN c%NOTFOUND;
END LOOP;
Ur's
AmarAlam
0 comments:
Post a Comment