| Improving comunication: BULK SQL (FORALL and BULK COLLECT ) |
Topics
BULK SQL:
- Minimizes overhead in PL/SQL <=> SQL comunication.
FORALL
: Send statements in batches.
FORALL
: faster than equivalent FOR LOOP
.
FORALL
: Can contain ONLY one DML statement.
SQL%BULK_ROWCOUNT
: Is like an AA. keeps the # of rows affected by each DML in the FORALL
statement.
SQL%BULK_EXCEPTIONS.[COUNT | ERROR_INDEX | ERROR_CODE]
: Is like an AA. keeps information about EXCEPTIONS that occurred during the FORALL
statement.
BULK COLLECT
: Receive results in batches
- Good when query/DML affects +4 database rows
FOR LOOP X FORALL |
SQL> set serveroutput on
SQL> DECLARE
TYPE NumList IS VARRAY(20) of number;
-- depts to be deleted
depts NumList := NumList(10,30,70);
BEGIN
FOR i IN depts.FIRST..depts.LAST LOOP
dbms_output.put_line('deleting dept '||i);
delete from emp_temp
where department_id = depts(i);
END LOOP;
END;
/
deleting dept 1
deleting dept 2
deleting dept 3
PL/SQL procedure successfully completed.
SQL> |
FORALL : Only one DML Statement.
- Use
SQL%BULK_ROWCOUNT to check # of affected rows.
SQL> set serveroutput on
SQL> DECLARE
TYPE NumList IS VARRAY(20) of number;
depts NumList := NumList(10,30,70);
BEGIN
FORALL i IN depts.FIRST..depts.LAST
delete from emp_temp
where department_id = depts(i);
-- How many rows were affected by
-- each DELETE statement?
FOR i IN depts.FIRST..depts.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Iteration #'
|| i || ' deleted ' ||
SQL%BULK_ROWCOUNT(i) ||'rows.');
END LOOP;
END;
/
Iteration #1 deleted 1 rows.
Iteration #2 deleted 2 rows.
Iteration #3 deleted 5 rows.
PL/SQL procedure successfully completed.
SQL> |
set serveroutput on
drop table parts1;
create table parts1 (pnum integer, pname varchar2(15));
drop table parts2;
create table parts2 (pnum integer, pname varchar2(15));
DECLARE
TYPE NumTab IS TABLE OF parts1.pnum%type INDEX BY PLS_INTEGER;
TYPE NameTab IS TABLE OF parts1.pname%type INDEX BY PLS_INTEGER;
pnums NumTab;
pnames NameTab;
iterations CONSTANT PLS_INTEGER := 50000;
t1 INTEGER;
t2 INTEGER;
t3 INTEGER;
BEGIN
--populate collection
FOR j IN 1..iterations LOOP
pnums(j) := j;
pnames(j) := 'Part No. ' || to_char(j);
END LOOP;
-- get t1 before start FOR LOOP
t1 := dbms_utility.get_time;
FOR i IN 1..iterations LOOP
insert into parts1 (pnum, pname)
values (pnums(i), pnames(i));
END LOOP;
-- get t2 before start FORALL
t2 := dbms_utility.get_time;
FORALL i IN 1..iterations
insert into parts2(pnum, pname)
values (pnums(i), pnames(i));
t3 := dbms_utility.get_time;
dbms_output.put_line('Execution Time (secs)');
dbms_output.put_line('---------------------');
dbms_output.put_line('FOR LOOP: ' || to_char((t2-t1)/100));
dbms_output.put_line('FORALL: ' || to_char((t3-t2)/100));
END;
/
anonymous block completed
Execution Time (secs)
---------------------
FOR LOOP: 1.74
FORALL: .06
PLS-00436: Implementation restriction - Cannot reference RECORD fields within a FORALL |
(...)
DECLARE
TYPE PartsRec IS RECORD (pnum parts1.pnum%type, name parts1.pname%type);
TYPE PartsRecTab IS TABLE OF PartsRec INDEX BY PLS_INTEGER;
precs PartsRecTab;
(...)
BEGIN
--populate collection
FOR j IN 1..iterations LOOP
precs(j).pnum := j;
precs(j).pname := 'Part No. ' || to_char(j);
END LOOP;
(...)
FORALL i IN 1..iterations
insert into parts2(pnum, pname)
values (precs(i).pnum, precs(i).pname);
(...)
END;
/
Error report:
ORA-06550: line 31, column 12:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND
table of records
Example 12-10 FORALL Statement for Subset of Collection
Exception Handling
| Bulk Statements: BULK COLLECT |
BULK COLLECT:
use the BULK COLLECT:
statement with SELECT statements
- Can be used (a) inside a SQL statement (implicit cursor) or (b) as part of a FETCH statement (explicit cursor).
- With FETCH: you can append the LIMIT clause to set the maximum number of rows read from the cursor at a time.
BULK COLLECT
Syntax:
DECLARE
-- on a SQL Statement:
SELECT column1 [, column2 [,...]]
COLLECT BULK INTO collection1 [, collection2 [,...]]
FROM
table_name
[WHERE where_clause_statements];
-- with explicit cursor:
FETCH cursor_name [(parameter1 [, parameter2 [,...]])]
BULK COLLECT INTO collection1 [, collection2 [,...]]
[LIMIT rows_to_return];
- The
BULK COLLECT INTO
statement is much faster than a standard cursor because it has one parse, execute, and fetch cycle.
- Scalar collections are the only supported SQL collection datatypes: No composite collections (record collections) (??).
FORALL:
Use the FORALL:
statement to INSERT, UPDATE or DELETE large data sets.
SQL%BULK_ROWCOUNT(i)
: Keeps the # of rows affected by each DML in the FORALL
statement.
SQL%BULK_EXCEPTIONS(i).[COUNT | ERROR_INDEX | ERROR_CODE]
: Keeps information about EXCEPTIONS that occurred during the FORALL
statement.
set serveroutput on
declare
-- Using BULK COLLECT with scalar collections
-- Here you will have to ensure that the discrete collections
-- remain synchronized
type empid_c is table of employees.employee_id%type;
type empdep_c is table of employees.department_id%type;
type empsal_c is table of employees.salary%type;
empid_ empid_c;
empdep_ empdep_c;
empsal_ empsal_c;
begin
-- selecting into the three collections, in parallel.
-- (Q) why would you do such a thing???
-- (A) The typical reason to opt for parallel collections is
-- to move the data from PL/SQL to external
-- programming languages or web applications.
select employee_id, department_id, salary
bulk collect into empid_, empdep_, empsal_
from employees;
for i in 1..empid_.count loop
dbms_output.put_line('id: ' || empid_(i) ||
' earns: '|| empsal_(i));
end loop;
end;
anonymous block completed
id: 100 earns: 24000
id: 101 earns: 17000
...
-- Alternativelly, you may use structured collections.
-- However, these cannot be shared with external programs.
set serveroutput on
declare
-- define one list of records. Synchronization is given.
type emprec is record(empid employees.employee_id%type,
dept employees.department_id%type,
sal employees.salary%type);
type emptab is table of emprec;
emptab_ emptab;
begin
-- collect directly into the structured table
select employee_id, department_id, salary
bulk collect into emptab_
from employees;
for i in 1..emptab_.count loop
dbms_output.put_line('id: ' || emptab_(i).empid ||
' earns: '|| emptab_(i).sal);
end loop;
end;
anonymous block completed
id: 100 earns: 24000
id: 101 earns: 17000
...
-- Now using an explicit cursor instead of SQL Stmt
-- Here the BULK COLLECT is used on the FETCH statement.
set serveroutput on
declare
-- As before, (why?), using parallel scalar collections.
-- remember they will need to be pretty well synchronized..
type empid_c is table of employees.employee_id%type;
type empdep_c is table of employees.department_id%type;
type empsal_c is table of employees.salary%type;
empid_ empid_c;
empdep_ empdep_c;
empsal_ empsal_c;
-- here define the explicit cursor c1
cursor c1 is
select employee_id, department_id, salary
from employees;
j pls_integer := 0;
begin
open c1;
loop
-- now when you fetch the cursor records,
-- use the BULK COLLECT clause and you may LIMIT the
-- number of rows fetched...
fetch c1 bulk collect into empid_, empdep_, empsal_ limit 10;
exit when empid_.count = 0;
j := j + 1;
dbms_output.put_line('Fetch # ' || j);
for i in 1..empid_.count loop
dbms_output.put_line('id: ' || empid_(i) ||
' earns: '|| empsal_(i));
end loop;
end loop;
end;
anonymous block completed
Fetch # 1
id: 100 earns: 24000
...
Fetch # 2
id: 110 earns: 8200
...
Fetch # 3
id: 120 earns: 8000
...
No comments:
Post a Comment