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

Using COMMIT The COMMIT statements end the present transaction and make permanent any changes made during that transaction. Till you commit the changes, other users cannot acc

Scoping Within the similar scope, all the declared identifiers should be unique. So, even if their datatypes differ, the variables and parameters cannot share the similar name.

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

Database Values You can use the SELECT statement to have the Oracle assign values to a variable. For Each and every item in the select list, there must be a matching, type-compa

Pass the nulls to a dynamic SQL: Passing Nulls: Assume that you want to pass the nulls to a dynamic SQL statement. For illustration, you may write the EXECUTE IMMEDIATE

Using %ROWTYPE The %ROWTYPE attribute gives a record type which represents a row in a table (or view). The record can store the whole row of data selected from the table or fetc

set serveroutput on declare a number(5); b number(5); c number(5); begin a:=&a; b:=&b; c:=a/b; dbms_output.put_line(c); exception when zero_d

Interesting properties of CROSS JOIN - SQL Compare these with the "interesting properties of JOIN", CROSS JOIN is associative but not commutative. Unlike JOIN and NATURAL JOI

ROWID The ROWID returns the rowid (binary address) of a row in the database table. You can use the variables of the type UROWID to store rowids in a readable format. In the il