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

Ask an Expert

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

Write your message here..

Related Questions

Perform exception handling with user-defined errors, On occasion, some of B... 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, Updating Variables For assignment, SQL uses the ke... 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.

I want to build website for funding, Project Description: I want to rebu... 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

Write a stored procedure, a. Create a table odetails_new. It has all the a... 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-pl/sql expressions , Operator Precedence The operati... 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 - functions, Parameter & Keyword Descriptio... 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 , Albeit simple method : These all the truth tabl... 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 of EXCEPT Operator - SQL Ex... 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, Initializing and Referencing Coll... 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, Semidifference via EXCEPT and JOI... Semidifference via EXCEPT and JOIN - SQL SELECT * FROM (SELECT StudentId FROM IS_CALLED WHERE Name = 'Devinder' EXCEPT DISTINCT CORRESPONDING SELECT StudentId