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
Negation (NOT, ¬) - SQL There are three rows instead of just two. As you can see, ¬ p is defined as in two-valued logic (2VL) when p is either true or false, but ¬ (unknown) i

(a) What are decision support systems, and what role do they play in the business environment? (b) Data warehousing is defined as "a subject-oriented, integrated, non-volatile c

Control Structures The Control structures are the most important PL/SQL extension to the SQL. Not only does PL/SQL let you manipulate Oracle data, it lets you process the data

Need for Dynamic SQL: You need dynamic SQL in the situations as follows: 1) You would like to execute a SQL data definition statement (like CREATE), a data control statemen

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

Pl/SQL Expressions The Expressions are constructed by using the operands and operators. An operand is a constant, literal, variable, or function call which contributes a value

LEVEL You use the LEVEL with the SELECT CONNECT BY statement to categorize rows from a database table into a tree structure. The LEVEL returns the level number of a node in a

Parameter SELF in pl/sql The MEMBER methods recognize a built-in parameter named SELF that is an instance of the object type. Whether declared explicitly or implicitly, it is

Datatype Conversion At times it is necessary to convert a value from one datatype to another. For e.g. if you want to inspect a rowid, you should convert it to a character stri

Records Records are the items of the type RECORD. The Records have exclusively named fields that can store the data values of various types. And hence, a record treat associate