RE: About Bulk Collect
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 20 Feb 2004 13:00:13 -0500
Seems to me, a context switch occurs every time a SQL statement is
executed from PL/SQL.
So, taking the example below:
-- Efficient method, using a bulk bind
FORALL i IN Id.FIRST..Id.LAST -- bulk-bind the VARRAY
UPDATE Emp_tab SET Sal =3D 1.1 * Sal
WHERE Mgr =3D Id(i);
The above does a single context switch and does a bulk operation.
-- Slower method, running the UPDATE statements within a regular loop
FOR i IN Id.FIRST..Id.LAST LOOP
UPDATE Emp_tab SET Sal =3D 1.1 * Sal
WHERE Mgr =3D Id(i);
END LOOP;
This, on the other hand, does a number of context switches equal to the
size of the Id array.
What's VOS?
-Mark
Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Imagination was given to man to compensate him for what he is not, and
a sense of humor was provided to console him for what he is." --Horace
Walpole
-----Original Message-----
From: Jared.Still@xxxxxxxxxxx [mailto:Jared.Still@xxxxxxxxxxx]=20
Sent: Friday, February 20, 2004 12:11 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: About Bulk Collect
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=20
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
=20
To: <oracle-l@xxxxxxxxxxxxx>
cc:=20
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 :=3D 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 =3D 1.1 * Sal
WHERE Mgr =3D 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 =3D 1.1 * Sal
WHERE Mgr =3D 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 =3D 7698;
BEGIN
-- Efficient method, using a bulk bind
SELECT Empno, Ename BULK COLLECT INTO Empno, Ename
FROM Emp_Tab WHERE Mgr =3D 7698;
-- Slower method, assigning each collection element within a loop.
counter :=3D 1;
FOR rec IN C LOOP
Empno(Counter) :=3D rec.Empno;
Ename(Counter) :=3D rec.Ename;
Counter :=3D Counter + 1;
END LOOP;
END;
DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList :=3D NumList(10, 30, 70); -- department numbers
BEGIN
FORALL i IN depts.FIRST..depts.LAST
DELETE FROM emp WHERE deptno =3D 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) :=3D j;
pnames(j) :=3D '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=20
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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
