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
Logical Operators The logical operators AND, NOT, and OR follow the tri-state logic shown in table below. The AND and OR are binary operators; NOT is a unary operator.

What Are Subprograms? The Subprograms are named PL/SQL blocks which can take parameters and be invoked. The PL/SQL has 2 types of subprograms known as the procedure s and func

Using raise_application_error The Package DBMS_STANDARD that is supplied with Oracle gives language facilities that help your application to interact with Oracle. For illustra

CLOSE Statement The CLOSE statement allows the resources held by a cursor variable or open cursor to be reused. No more rows can be fetched from the cursor variable or closed

Other monadic - SQL In 2VL there are just 4 (2 2 ) monadic operators, of which negation is really the only "useful" one. When a third truth value is introduced we have 27 (3 3

Declaring Records Whenever you define a RECORD type, you may declare records of that type, as the illustration shows: DECLARE TYPE StockItem IS RECORD ( item_no INTEG

Declaring Exceptions The Exceptions can be declared only in the declarative part of the PL/SQL subprogram, block, or package. By introducing its name, you can declare an excep

ROWNUM The ROWNUM returns a number representing the order in which a row was selected from the table. The first row selected has a ROWNUM of 1; the second row has a ROWNUM of

Closest Approximation to Relational Union - SQL Actually, just as SQL has several varieties of JOIN, it also has several varieties of UNION, none of which is equivalent to th

%TYPE Attribute The %TYPE attribute gives the datatype of a record, field, nested table, database column, or the variable. You can use the %TYPE attribute as the datatype speci