Use bulk binds - improve performance of application, PL-SQL Programming

Use Bulk Binds

If SQL statements execute inside a loop using the collection elements as bind variables, context switching between the PL/SQL & SQL engines can slow down the execution. For illustration, the UPDATE statement below is sent to the SQL engine by 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

...

UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);

END LOOP;

END;

In such situation, if the SQL statement affects five or more database rows, the use of bulk binds can improve the performance significantly. For illustration, the UPDATE statement below is sent to the SQL engine merely once, with the whole nested table:

FORALL i IN depts.FIRST..depts.LAST

UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);

Posted Date: 10/5/2012 4:55:47 AM | Location : United States







Related Discussions:- Use bulk binds - improve performance of application, Assignment Help, Ask Question on Use bulk binds - improve performance of application, Get Answer, Expert's Help, Use bulk binds - improve performance of application Discussions

Write discussion on Use bulk binds - improve performance of application
Your posts are moderated
Related Questions
On occasion, some of Brewbean's customers mistakenly leave an item out of a basket already checked out, so they create a new basket containing the missing items. However, they requ

Updating Variables For assignment, SQL uses the key word SET, as in SET X = X + 1 (read as "set X equal to X+1") rather than X: = X + 1 as found in many computer languages.

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

a. Create a table odetails_new. It has all the attributes of odetails and an additional column called cost, whose values are the product of the quantity and price of the part bein

Operator Precedence The operations within an expression are completed in a particular order depending on their precedence (priority). The table shows the default order of the op

Parameter & Keyword Description: function_name: The user-defined function is identifying by that keyword. parameter_name: This identifies the formal parameter that

Albeit simple method : These all the truth tables give us our first as albeit simple method for proving a theorem: where check whether it can be written in propositional logic

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

Initializing and Referencing Collections Until you initialize a collection, a nested table or varray is automatically null (i.e. the collection itself is null, not its elements)

Semidifference via EXCEPT and JOIN - SQL SELECT * FROM (SELECT StudentId FROM IS_CALLED WHERE Name = 'Devinder' EXCEPT DISTINCT CORRESPONDING SELECT StudentId