Bulk binds advantages, PL-SQL Programming

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.

1509_bulk bind advantage.png

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







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
Related Questions
#quesWrite a cursor to open an employee database and fetch the employee record whose age is greater than 45.tion..

Project Description: I want to rebuild it and add better content to it It will include up to 5 forms The data will be saved on SQL server and the data access layer could b

JOIN and AND in SQL In this Section is all about one operator, JOIN. SQL's closest counterpart, NATURAL JOIN, has already been covered. Here we look at several other "join" op

Example of NOT EXISTS in SQL Example: Use of NOT EXISTS CREATE ASSERTION Must_be_enrolled_to_take_exam_alternative1 CHECK ( NOT EXISTS (SELECT StudentId, CourseId

Parameter and Keyword Description: EXIT: An unconditional EXIT statement (i.e., one without a WHEN clause) exits the present loop instantly. The Execution resumes with th

Parameter and Keyword Description: dynamic_string: This is a string variable, literal, or expression which represents a SQL statement or the PL/SQL block. define_vari

Information Hiding   With the information hiding, you see only the details that are significant at a given level of algorithm and data structure design. The Information hiding

Packages The package is a schema object which groups logically associated to the PL/SQL items, types, and subprograms. The Packages have 2 sections: the specification & the bod

Introduction Oracle 9i - it was made public in the year 2001 with over 400 features, and graphics, it has merged the traditional business with modern internet application

Defining REF CURSOR Types To make cursor variables, you take 2 steps. At first, you define a REF CURSOR type, and then declare the cursor variables of that type. You can defin