Bulk Binds advantages

In the Embedded Oracle RDBMS, the PL/SQL engines accept any valid PL/SQL subprogram or block. As the figure shows, the PL/SQL engine executes all procedural statements but sends SQL statements to the SQL engine that executes the SQL statements and, in many cases, returns the data to the PL/SQL engine.

Figure: Context Switching

Each of the context switches between the PL/SQL and SQL engines adds to the overhead. Therefore, if many switches are needed, the performance suffers. That can happen when the SQL statements execute inside a loop using the collection (nested table, index-by table, varray, or the host array) elements as the bind variables. For e.g., the DELETE statement below is sent to the SQL engine with each and every iteration of the FOR loop:

DECLARE

TYPE NumList IS VARRAY(20) OF NUMBER;

depts NumList := NumList(10, 30, 70, ...); -- department numbers

BEGIN

...

FOR i IN depts.FIRST..depts.LAST LOOP

...

DELETE FROM emp WHERE deptno = depts(i);

END LOOP;

END;

In such cases, when the SQL statement affects five or more database rows, the use of bulk binds can improve the performance significantly.

Posted Date: 10/4/2012 3:25:35 AM | Location : United States

Ask an Expert

Related Discussions:- Bulk binds advantages, Assignment Help, Ask Question on Bulk binds advantages, Get Answer, Expert's Help, Bulk binds advantages Discussions

Write discussion on Bulk binds advantages
Your posts are moderated

Write your message here..

Related Questions

Mutual recursion, Mutual Recursion The Subprograms are mutually recursi... Mutual Recursion The Subprograms are mutually recursive if they directly or indirectly call each other. In the illustration below, the Boolean functions odd & even, that dete

Assignment source not a literal - variable, Assignment Source Not a Literal... Assignment Source Not a Literal - Variable Syntax: SET SN = SID (SUBSTRING (SN.C FROM 1 FOR 1)||'5');

First step at defining type sid in sql, First Step at defining type SID in ... First Step at defining type SID in SQL CREATE TYPE SID AS ( C VARCHAR(5) ) ; Explanation: TYPE SID announces that a type named SID is being defined to the syst

Benefit of the dynamic sql pl sql, Benefit of the dynamic SQL: This pa... Benefit of the dynamic SQL: This part shows you how to take full benefit of the dynamic SQL and how to keep away from some of the common pitfalls. Passing the Names of Sc

Updating a variable, Updating a Variable Assignment of an attribute va... Updating a Variable Assignment of an attribute value in a variable of a structured type Synatx: SET SN.C = 'S2'; As in Example the entire statement is equivalent to a

Substitution and instantiation - sql, Substitution and Instantiation - SQL ... Substitution and Instantiation - SQL It shows how NULL might appear in substitution for a parameter of a predicate and how it might thus participate in instantiation of that p

What is a collection, What Is a Collection The collection is an ordered... What Is a Collection The collection is an ordered group of elements, all of similar type (for e.g. the grades for a class of students). Each element has a unique subscript whic

CURSOR, #quesWrite a cursor to open an employee database and fetch the empl... #quesWrite a cursor to open an employee database and fetch the employee record whose age is greater than 45.tion..

I want database development with analysis tools, Project Description: I ... Project Description: I want a database for large governmental and private data sets on one country that will be easily extended to other countries in the future. Also, the datab

Pragma restrict_references in pl sql, Using Pragma RESTRICT_REFERENCES: ... Using Pragma RESTRICT_REFERENCES: The function called from the SQL statements should obey certain rules meant to control the side effects. To check for violation of the rules,