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
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

SQL Is a Database Language: The commands given to a DBMS by an application are written in the database language of the DBMS. The term data sublanguage is sometimes used instea

BETWEEN Operator The operator BETWEEN, tests whether the value lies in a specified series. That means "greater than or equivalent to low value and less than or equivalent to hig

Hi there, I have the final part of a submission to do, it is a demonstration that takes place tomorrow. I do not have to use previous information, but i have resources that sho

In Packages The Forward declarations also group logically related subprograms in the package. The subprogram specifications go in the package specification, & the subprogram b

Short-Circuit Evaluation When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops computing the expression as soon as the result

Overview of control structures According to the structure theorem, any computer program can be written by using the basic control structures as shown in figure below. They can b

Parameter Default Values As the illustration below shows, you can initialize the IN parameters to the default values. In that way, you can pass various numbers of actual par

Using NOT NULL Besides assigning an initial value, the declarations can impose the NOT NULL constraint, as the example below shows: acct_id INTEGER(4) NOT NULL := 9999; You ca

Existential Quantification - SQL Existential quantification-stating that something is true of at least one object under consideration-can be expressed by OR(r,c), meaning tha