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

Logical Connectives - SQL SQL's extended truth tables in which the symbol, for unknown, appears along with the usual T and F. Negation (NOT, ¬) Conjunction (

Using a Host Variable You can declare the cursor variable in the PL/SQL host environment like an OCI or Pro C program. To use the cursor variable, you should pass it as a host

At times, Brewbean's has changed the id number for existing products. In the past, they have had to add a new product row with the new id to the BB_PRODUCT table, modify all the co

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

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

Iterative Control: LOOP  Statements The LOOP statement executes a series of statements multiple times. There are 3 forms of LOOP statements: LOOP, WHILE-LOOP, & FOR-LOOP. LOOP

Using a join on 2 tables, select all columns and 10 rows from the 2 tables without the use of a Cartesian product. Query: SELECT * FROM EMPLOYEE1 E JOIN STAFF S ON E.EMP_

Benefit of the dynamic SQL: This part shows you how to take full benefit of the dynamic SQL and how to keep away from some of the common pitfalls. Passing the Names of Sc

Literals A literal is an explicit numeric, string, character, or Boolean value not represented by an identifier. Numeric literal 147 and the Boolean literal FALSE are some of