Let's see if I can be the first to ask this question: ;) How would you define a context switch in this situation? Are values from registers in the VOS ( don't know if VOS actually has registers ) pushed onto a stack, and it's address saved for later execution? Each time a SQL statement is executed? Jared "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx> Sent by: oracle-l-bounce@xxxxxxxxxxxxx 02/20/2004 07:03 AM Please respond to oracle-l To: <oracle-l@xxxxxxxxxxxxx> cc: Subject: About Bulk Collect Here is a very brief introduction to bulk collect if you never heard about it. There are to engines to run PL/SQL blocks and subprograms. PL/SQL engine runs procedural statements, while the SQL engine runs SQL statements. During execution, every SQL statement causes a context switch between the two engines. Performance can be improved reducing the number of contexts switches using FORALL for bulk collection. Without the bulk bind, PL/SQL sends a SQL statement to the SQL engine for each DML( Insert, Update, Delete) command you can use too with SELECT statements. DECLARE TYPE Numlist IS VARRAY (100) OF NUMBER; Id NUMLIST := NUMLIST(7902, 7698, 7839); BEGIN -- Efficient method, using a bulk bind FORALL i IN Id.FIRST..Id.LAST -- bulk-bind the VARRAY UPDATE Emp_tab SET Sal = 1.1 * Sal WHERE Mgr = Id(i); -- Slower method, running the UPDATE statements within a regular loop FOR i IN Id.FIRST..Id.LAST LOOP UPDATE Emp_tab SET Sal = 1.1 * Sal WHERE Mgr = Id(i); END LOOP; END; DECLARE TYPE Var_tab IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; Empno VAR_TAB; Ename VAR_TAB; Counter NUMBER; CURSOR C IS SELECT Empno, Ename FROM Emp_tab WHERE Mgr = 7698; BEGIN -- Efficient method, using a bulk bind SELECT Empno, Ename BULK COLLECT INTO Empno, Ename FROM Emp_Tab WHERE Mgr = 7698; -- Slower method, assigning each collection element within a loop. counter := 1; FOR rec IN C LOOP Empno(Counter) := rec.Empno; Ename(Counter) := rec.Ename; Counter := Counter + 1; END LOOP; END; DECLARE TYPE NumList IS VARRAY(20) OF NUMBER; depts NumList := NumList(10, 30, 70); -- department numbers BEGIN FORALL i IN depts.FIRST..depts.LAST DELETE FROM emp WHERE deptno = depts(i); END; 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; BEGIN FOR j IN 1..5000 LOOP -- load index-by tables pnums(j) := j; pnames(j) := 'Part No. ' || TO_CHAR(j); END LOOP; FORALL i IN 1..5000 -- use FORALL statement INSERT INTO parts VALUES (pnums(i), pnames(i)); FOR i IN 1..5000 LOOP -- use FOR loop INSERT INTO parts VALUES (pnums(i), pnames(i)); END LOOP; END; To use bulk operation read PL/SQL users guides and reference, to know about more features of bulk operations. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------