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
a. Write an anonymous block that contains a PL/SQL function. Given an order number orderNo, the function will calculate the total number of the parts in the order. Then the anonym

Example of ADD CONSTRAINT in SQL Example: Alternative formulation for MAX_ENROLMENTS ALTER TABLE IS_ENROLLED_ON ADD CONSTRAINT MAX_ENROLMENTS CHECK ((SELECT COUNT (*)

Case Sensitivity Similar to all the identifiers, the variables, the names of constants, and parameters are not case sensitive. For illustration, PL/SQL considers the following n

UTL_FILE: The Package UTL_FILE permits your PL/SQL programs to read & write operating system (OS) text files. It gives a restricted version of the standard OS stream file I/O,

Example of EXCEPT Operator - SQL Example, like its counterpart in the theory book, illustrates the convenience of allowing any table expression to be the source for an INSERT

Magento change address format depending on store Project Description: What I need is that depending on the store in which the customer bought the address should change the fo

Cursor Variables As Parameters You can declare the cursor variables as the formal parameters of the functions and procedures. In the illustration below, you define the REF CUR

DBMS_PIPE: The Package DBMS_PIPE allows various sessions to communicate over the named pipes. (A pipe is a region of memory used by one of the process to pass information to

PITS Depressions in secondary cell wall is called pit. A pit present on the free cell wall surface without its partner is called Blind pit. It consists of 2 parts -

Inner Join We have learned how to retrieve data from one table by using SELECT statement. But, as we have learned, normalized relational databases mean the data is spread betw