Oracle 10g BULK Binding for better Performance

  Performance is always a very important key in the design and development of code . Irrespective of the language , it is very important while we have database operations . Oracle’s last few releases like 9i and 10g came up with a new built in features to improve the performance. The new features include

• RETURNING CLAUSE
• BULK BINDING

Of course design always plays very crucial role for performance.

RETURNING CLAUSE

By thumb rule ,we can improve performance by minimizing explicit calls to database. If we need to get the information about the row that are impacted by DML operations (INSERT, UPDATE, DELETE) ,we can run a additional SELECT Clause after DML operations. RETURNING is a feature which helps us to avoid the SELECT clause after the DML operations.

We can include RETURNING clause in DML statements. Itt returns column values from the affected rows into a pl/sqlvariable. This avoids the use of additional SELECT clause and finally fewer network trip, less server resources.

Below are examples about how to use RETURNING CLAUSE.

——————-

create or replace

PROCEDURE update_item_price(p_header_id NUMBER) IS

type itemdet_type is RECORD

 (

ordered_item order_test.ordered_item%TYPE,

unit_selling_price order_test.unit_selling_price%TYPE,

l ine_id order_test.line_id%TYPE

);

recITEMDET itemdet_type;

BEGIN

–

UPDATE order_test

SET unit_selling_price = unit_selling_price+100

WHERE header_id = p_header_id

RETURNING ordered_item,unit_selling_price, line_id INTO recITEMDET;

dbms_output.put_line(‘Ordered Item – ‘||recITEMDET.ordered_item||’

‘||recITEMDET.unit_selling_price|| ‘ ‘||recITEMDET.line_id);

INSERT into order_test (ordered_item,unit_selling_price, line_id, header_id)

values (‘ABCD’,189,9090,1)

RETURNING ordered_item,unit_selling_price, line_id INTO recITEMDET;

dbms_output.put_line(‘Ordered Item – ‘||recITEMDET.ordered_item||’

‘||recITEMDET.unit_selling_price|| ‘ ‘||recITEMDET.line_id);

DELETE from order_test where header_id = 119226 RETURNING ordered_item,unit_selling_price, line_id into recITEMDET;

dbms_output.put_line(‘Ordered Item – ‘||recITEMDET.ordered_item||’ ‘||recITEMDET.unit_selling_price|| ‘ ‘||recITEMDET.line_id);

END;

– End of Example 1 —

BULK BINDING

When we talk about oracle database ,Our code is a combination of PL/SQL and SQL . Oracle server uses two engines to run PL/SQL blocks , subprograms , packages etc.

• PL/SQL engine executes the procedural statements, but passes the SQL statements to SQL engine.

• SQL engine executes the sql statements and if required, returns data to PL/SQL engine.

The execution of pl/sql code results in switch between these two engines,If we have SQL statement in LOOP like structures switching between these two engines results in performance penalty for excessive amount of SQL processing. This makes more sense when we have a SQL statement in a loop that uses indexed collections element values (e.g index-by tables, nested tables , varrays).

We can improve the performance to great extent by minimizing the number of switches between these two engines. Oracle has introduced the concept of Bulk Binding to reduce the switching between these engines.

Bulk binding passes the entire collection of values back and forth between the two engines in a single context switch rather than switching between the engines for each collection values in an iteration of a loop.

Syntax for BULK operations are ,

FORALL index low..high

sql_statement

..bulk collection INTO collection_name

Please note down that although FORALL statement contains an iteration scheme, it is not a FOR LOOP. Looping is not required at all when using Bulk Binding.

FORALL instruct pl/sql engine to bulk bind the collection before passing it to SQL engine, and

BULK COLLECTION instruct SQL engine to bulk bind the collection before returning it to

PL/SQL engine.

we can improve performance with bulk binding in DML as well as SELECT statement as shown in examples below.

declare type line_rec_type is RECORD

(line_id NUMBER,

ordered_item varchar2(200),

header_id NUMBER,

attribute1 varchar2(100));

type line_type is table of line_rec_type

index by pls_integer;

i pls_integer:=1;

l_att varchar2(100);

l_line_id number;

l_linetbl line_type;

l_linetbl_l line_type;

type line_type_t is table of integer

index by pls_integer;

j pls_integer:=1;

l_lin_tbl line_type_t;

type line_type_t2 is table of oe_order_lines_all.attribute2%TYPE

index by pls_integer;

j pls_integer:=1; l_lin_tbl2 line_type_t2;

begin

dbms_output.put_line(‘Test’);

for line in (select attribute10, line_id , ordered_item, header_id

from oe_order_lines_all

where creation_date between sysdate-10 and sysdate)

loop

l_linetbl(i).line_id:=line.line_id;

l_linetbl(i).header_id:=line.header_id;

l_linetbl(i).ordered_item:=line.ordered_item;

l_lin_tbl(i):=line.line_id;

i:=i+1;

end loop;

dbms_output.put_line(‘Total count in table ‘||l_lin_tbl.COUNT);

– Below statement will call the Update Statement ONLY Once for complete Collection.

forall i in l_lin_tbl.FIRST..l_lin_tbl.LAST

save exceptions

update oe_order_lines_all

set attribute1=l_lin_tbl(i)

where line_id = l_lin_tbl(i);

–Common Error

–DML statement without BULK In-BIND cannot be used inside FORALL

–implementation restriction;cannot reference fields of BULK In_BIND table of records

–In below statement we are passing complete collection to pl/sql table in Single statement and

thus avoiding the Cursor.

SELECT line_id, ordered_item, header_id, attribute1 BULK COLLECT INTO l_linetbl_l

FROM oe_order_lines_all

WHERE creation_date between sysdate-10 and sysdate;

FOR i in 1..l_linetbl_l.count LOOP

dbms_output.put_line(‘ Line ID = ‘||l_linetbl_l(i).line_id||’ Ordered Item =

‘||l_linetbl_l(i).ordered_item||’ Attribute1 =’||l_linetbl_l(i).attribute1);

END LOOP;

–Returning

forall i in l_lin_tbl.FIRST..l_lin_tbl.LAST UPDATE oe_order_lines_all

SET ATTRIBUTE2 = l_lin_tbl(i)

WHERE line_id = l_lin_tbl(i)

RETURNING line_id BULK COLLECT into l_lin_tbl2;

FOR i in 1..l_lin_tbl2.count LOOP

dbms_output.put_line(‘ Attribute2 =’||l_lin_tbl2(i));

END LOOP;

END;

– End of Example 2 —