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 , and it is very important when we have database operations.
Oracle in last few releases of database like 9i and 10g came up with New Built in features to improve the performances , like


* RETURNING CLAUSE
* BULK BINDING

and 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 have requirement to get the information about the row that are impacted by DML operations (INSERT, UPDATE, DELETE) , we can do SELECT statement after DML operations , but in that case we need to run a additional SELECT Clause.RETURNING is a feature which helps us to avoid the SELECT clause after the DML operations.
We can include RETURNING clause in DML statements , it returns column values from the affected row in pl/sql variable, thus eliminate the need for additional SELECT statement to retrieve the data 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,
line_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 ---

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

* PL/SQL engine to run 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.

thus in execution of pl/sql code our code results in switch between these two engines, and if we have SQL statement in LOOP like structure 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, nexted tables , varrays).

We can improve the performance to great extends by minimizing the number of switches between these 2 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 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 statment 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 ststement without BULK In-BIND canot 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;