How bulk bind helps improvement in performance?, PL-SQL Programming

How Bulk Binds Improve Performance

The assigning of values to the PL/SQL variables in SQL statements is known as binding.

The binding of the whole collection at once is known as the bulk binding. The Bulk binds improve performance by minimizing the number of the context switches between the PL/SQL and SQL engines. With the bulk binds, whole collections, not just an individual element, are passed back and forth. For illustration, the DELETE statement below is sent to the SQL engine just once, with the whole nested table:

DECLARE

TYPE NumList IS TABLE OF NUMBER;

mgrs NumList := NumList(7566, 7782, ...); -- manager numbers

BEGIN

...

FORALL i IN mgrs.FIRST..mgrs.LAST

DELETE FROM emp WHERE mgr = mgrs(i);

END;

In the illustration below, 5000 part numbers and names are loaded into the index-by tables. Then, all the table elements are inserted into a database table twice. At First, they are inserted using a FOR loop, that completes in 38 seconds. Then, they are bulk-inserted by using a FORALL statement that completes in only 3 seconds.

SQL> SET SERVEROUTPUT ON

SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));

Table created.

SQL> GET test.sql

1 DECLARE

2 TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;

3 TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;

4 pnums NumTab;

5 pnames NameTab;

6 t1 CHAR(5);

7 t2 CHAR(5);

8 t3 CHAR(5);

9 PROCEDURE get_time (t OUT NUMBER) IS

10 BEGIN SELECT TO_CHAR(SYSDATE,'SSSSS') INTO t FROM dual; END;

11 BEGIN

12 FOR j IN 1..5000 LOOP -- load index-by tables

13 pnums(j) := j;

14 pnames(j) := 'Part No. ' || TO_CHAR(j); 15 END LOOP;

16 get_time(t1);

17 FOR i IN 1..5000 LOOP -- use FOR loop

18 INSERT INTO parts VALUES (pnums(i), pnames(i));

19 END LOOP;

20 get_time(t2);

21 FORALL i IN 1..5000 -- use FORALL statement

22 INSERT INTO parts VALUES (pnums(i), pnames(i));

23 get_time(t3);

24 DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');

25 DBMS_OUTPUT.PUT_LINE('---------------------');

26 DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR(t2 - t1));

27 DBMS_OUTPUT.PUT_LINE('FORALL: ' || TO_CHAR(t3 - t2));

28* END;

SQL> /

Execution Time (secs)

---------------------

FOR loop: 38

FORALL: 3

PL/SQL process successfully completed.

In the bulk-bind input collections, you can use the FORALL statement. And in the bulk-bind output collections, you can use the BULK COLLECT clause.

Posted Date: 10/4/2012 3:27:07 AM | Location : United States







Related Discussions:- How bulk bind helps improvement in performance?, Assignment Help, Ask Question on How bulk bind helps improvement in performance?, Get Answer, Expert's Help, How bulk bind helps improvement in performance? Discussions

Write discussion on How bulk bind helps improvement in performance?
Your posts are moderated
Related Questions
Authorize and fetch data from Instagram Project Description: Incorporate Instagram feed on mobile site platform: c#, ms sql, jquerymobile, jquery Web admin Author

Cursor FOR Loops In most cases that need an explicit cursor, you can simplify the coding by using a cursor FOR loop rather of the OPEN, FETCH, and CLOSE statements. A cursor FO

Keyword and Parameter Description: label_name: This is an undeclared identifier which labels an executable statement or the PL/SQL block. You can use a GOTO statement to

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.

Error Handling The PL/SQL makes it easy to detect and process the predefined and user-defined error conditions known as exceptions. Whenever an error occurs, an exception is ra

Transaction Control The Oracle is transaction oriented; that is, Oracle uses the transactions to make sure the data integrity. The transaction is a sequence of SQL data manip

(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

Cursors The Oracle uses work areas to execute the SQL statements and to store process information. A PL/SQL construct known as the cursor. Let's you assume name a work area and

What Is a Package? The package is a schema object that group logically related PL/SQL items, types, and subprograms. The Packages usually have 2 parts, a specification & a bo

Blocks: The fundamental program unit in the PL/SQL is the block. The PL/SQL block is defined by the keywords BEGIN, DECLARE, EXCEPTION, and END. These keywords partition the b