(ref) PL/SQL Optimization

[ Oracle PL/SQL ]

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>


Comparing INSERT performance: FOR LOOP X FORALL
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