Re: About Bulk Collect

  • From: Jared.Still@xxxxxxxxxxx
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 20 Feb 2004 09:11:29 -0800

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
-----------------------------------------------------------------

Other related posts: