Bulk Binding
This article begins a discussion of how to work with collections. Previous versions of Oracle had limitations for collection usage in regards to SQL statement processing. Bulk Bind and Collect features were introduced to reduce the SQL processing overhead by efficient use of collections in PL/SQL code.
The PL/SQL engine executes procedural statements and sends all SQL statements present in the code to the SQL engine. The SQL engine will parse and execute the query or DML statement and return the expected output back to the PL/SQL engine. This switch between the two engines is called context switching.
We mostly concentrate on the SQL statement to tune performance issues. It is worth noting that excessive context switching can affect performance. This would be substantially significant when we are carrying out SQL statements in a loop. The features discussed below were introduced to reduce this overhead of SQL processing. Introduced in Oracle 8i, these features are being improved on with every new release.
We mostly concentrate on the SQL statement to tune performance issues. It is worth noting that excessive context switching can affect performance. This would be substantially significant when we are carrying out SQL statements in a loop. The features discussed below were introduced to reduce this overhead of SQL processing. Introduced in Oracle 8i, these features are being improved on with every new release.
Two PL/SQL features, Bulk Bind and Bulk collect help in improving performance and utilizing resources effectively from within PL/SQL code. These features reduce context switching, (i.e., multiple SQL statements being executed from the code resulting in calls to SQL engine), and carry out the operation on the concerned object at one go. Since the SQL statements are fired in a bundle, frequent access to SQL engine is reduced.
In cases where the PL/SQL code is being executed on a different terminal than the server itself, this would also result in optimal network usage rather than too frequent network calls to access the SQL engine.
Bulk Collects (Reading data in bulk)
The bulk collect option instructs the SQL engine to bulk bind the output collections before returning them to the PL/SQL engine. This allows us to load data dynamically into collections at one shot for further processing. Bulk collect can be used with FETCH INTO and RETURNING INTO statements.
Syntax:
... bulk collect into collection...
For example, let us assume that we need to load all pending transactions into a temporary table and process them one by one. As part of validation, there is a need to refer to the data in the same table, from time to time, for each transaction being processed. One possible method to write the code would be to load all of the data in the temporary table to a collection type. This way, additional queries on the table could be avoided (context switch) and the processing could be carried out in PL/SQL itself. This idea is further improved on by the use of the bulk collect option, as all data is loaded into PL/SQL at the same time.
Examples of Bulk
Bulk used in Cursors
declare
cursor cf is select * from emp;
type emp_tab is table of emp%rowtype index by binary_integer;
V emp_tab;
v_limit natural := 10;
begin
open cf;
fetch cf bulk collect into V limit v_limit;
for j in V.first .. V.last
loop
dbms_output.put_line(V(j).ename);
end loop;
end;
Bulk Insert
Create table BI (a number check(a between 5 and 45));
declare
type no_list is table of number index by binary_integer;
v no_list;
bulk_errors exception;
pragma exception_init ( bulk_errors, -24381 );
begin
for i in 5..50
loop
v(i) := i;
end loop;
forall j in V.first .. V.last save exceptions
insert into bi values (V(j));
dbms_output.put_line('Records inserted');
exception
when bulk_errors then
for j in 1..sql%bulk_exceptions.count
loop
Dbms_Output.Put_Line ( 'Error from element #' ||
To_Char(sql%bulk_exceptions(j).error_index) || ': ' ||
Sqlerrm(-sql%bulk_exceptions(j).error_code) );
end loop;
end;
Bulk Delete
declare
type emp_tab is table of emp%rowtype index by binary_integer;
V emp_tab;
begin
delete from emp
returning empno,ename,job,mgr,hiredate,sal,comm,deptno bulk collect into V;
for i in V.first .. v.last
loop
dbms_output.put_line(V(i).ename);
end loop;
end;
/
Discussion about this article
| Author | Body |
Rahul
8/29/2009 9:24 PM
|
Please provide feedback about this article here.
To participate in this discussion Sign up for free membership of 24x7code.
To Signup click on Login , Use create user link & the follow the instructions.
Thank you
|