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

Level - sql pseudocolumns, LEVEL You use the LEVEL with the SELECT CON... 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, Using COMMIT The COMMIT statements end the present trans... 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-naming conventions, Scoping Within the similar scope, all the de... 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 structure, Control Structures The Control structures are the mo... 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-assignments in pl/sql, Database Values You can use the S... 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, Pass the nulls to a dynamic SQL: Pas... 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-declarations in sql, Using %ROWTYPE The %ROWTYPE attribut... 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

Exception handling, set serveroutput on declare a number(5); b n... 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, Interesting properties of CROSS... 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 - sql pseudocolumns, ROWID The ROWID returns the rowid (binary a... 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