-----------------------------------------------------
Using Oracle BULK COLLECTtechnique enables the PL/SQL engine to collect many rowsat 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 generalconcept 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 withFORALL 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 SQLengine. 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 resultthe execution time has been reduced from 40 minutes to 30 seconds only by using BULK COLLECT andFORALL.
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;
No comments:
Post a Comment