Saturday, June 20, 2009

Tuning by Batch processing in PL/sqL (BULK COLLECT, FORALL and Associative Arrays)

Tuning Batch processing in PL/sqL
(BULK COLLECT, FORALL and Associative Arrays)

BULK COLLECT (for Batch Query processing)
==================================
BULK COLLECT reduces FETCH operations by fetching many rows at a time into a collection.It should always be used in conjunction with the LIMIT clause where there is a risk of fetching too many rows, as the resultant collection would exceed memory limitations and start paging to disk.Anywhere from 100 to 1000 is a practical LIMIT; it is not important to find the "perfect" number as there is a law of diminishing returns.

BULK COLLECT with the LIMIT clause is only available with explicit FETCH statements, which is annoying because it precludes the tidy and intuitive Implicit Cursor FOR loops.

1. Implicit Cursor FOR Loop: tidy, intuitive, and very, very, slow
------------------------------------------------------------------
FOR empl IN (
SELECT ename
FROM emp
) LOOP
<... some statements ...>
END LOOP;

2. Implicit Cursor BULK COLLECT: efficient, but not scalable
---------------------------------------------------------
SELECT ename
BULK COLLECT INTO empl_tab
FROM emp;

FOR i IN empl_tab.FIRST .. empl_tab.LAST LOOP
<... some statements ...>
END LOOP;

3. Explicit Cursor BULK COLLECT with LIMIT: fast and scalable, but wordy and complex
----------------------------------------------------------------------------

DECLARE
CURSOR empl_cur IS
SELECT ename
FROM emp;

TYPE empl_tab_typ IS TABLE OF EMPL_CUR%ROWTYPE;
empl_tab EMPL_TAB_TYPE;
BEGIN
OPEN empl_cur;
LOOP
FETCH empl_cur BULK COLLECT INTO empl_tab LIMIT 1000;
EXIT WHEN empl_tab.COUNT = 0;

FOR i IN empl_tab.FIRST .. empl_tab.LAST LOOP
<... some statements ...>
END LOOP;

EXIT WHEN empl_cur%NOTFOUND;
END LOOP;
CLOSE empl_cur;
END;

Another Example :
-----------------------------

CREATE OR REPLACE FUNCTION stlbas.dfn_month_bal_pf_anl (
p_compcode IN VARCHAR2,
p_corcod IN VARCHAR2,
p_fromdate IN DATE,
p_todate IN DATE,
p_deb_cre IN VARCHAR2
)
RETURN NUMBER
IS
CURSOR my_cur
IS
SELECT SUM (NVL (debit, 0)) - SUM (NVL (credit, 0)) bal_d,
SUM (NVL (credit, 0)) - SUM (NVL (debit, 0)) bal_c
FROM mv_sttrailb
WHERE compcode = DECODE (p_compcode, 'All', compcode, p_compcode)
AND glcode = p_corcod
AND doctdate BETWEEN p_fromdate AND p_todate;

TYPE my_cur_type IS TABLE OF my_cur%ROWTYPE;

my_rec my_cur_type;
BEGIN
OPEN my_cur;

LOOP
FETCH my_cur
BULK COLLECT INTO my_rec LIMIT 1000;

FOR i IN 1 .. my_rec.COUNT
LOOP
IF p_deb_cre = 'D'
THEN
RETURN (my_rec (i).bal_d);
ELSIF p_deb_cre = 'C'
THEN
RETURN (my_rec (i).bal_c);
END IF;
END LOOP;

EXIT WHEN my_cur%NOTFOUND;
END LOOP;
END;
/

The FORALL Statement ( for INSERT, UPDATE, and DELETE) in pl/sql
=====================================================

Unlike the FOR loop, FORALL is more truly a statement than a loop; it can only repeat a single DML statement. Every INSERT, UPDATE, and DELETE statement in a PL/SQL program performs a context switch from the PL/SQL engine to the SQL engine. But by loading all of the rows to be processed into a collection and performing the DML inside a FORALL statement, the DML and the collection are passed to the SQL engine and run with a single context switch. Exceptions can be captured using the SAVE EXCEPTIONS clause and are returned in the SQL%BULK_EXCEPTIONS collection.

DECLARE
empl_tab_type IS TABLE OF emp_cur%ROWTYPE; -- collection of records
empno_tab_type IS TABLE OF emp.empno%TYPE; -- collection of scalars

empl_tab EMPL_TAB_TYPE;
empno_tab EMPNO_TAB_TYPE;

-- create an exception handler for ORA-24381
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);

BEGIN
: :
FORALL i IN empl_tab.FIRST .. empl_tab.LAST SAVE EXCEPTIONS
UPDATE emp
SET ROW = empl_tab(i)
WHERE empno = empno_tab(i);
: :

EXCEPTION
WHEN dml_errors THEN
FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
<... handle the errors ...>
END LOOP;
END;

As demonstrated in this example, it is currently a limitation of PL/SQL that SQL statements can reference only entire rows of a collection, not individual data items within a record. Entire rows are useful when updating with SET ROW (as above) or record-wise inserting, but you must maintain separate scalar collections to reference individual values in the WHERE clause. Many find it easier to use only scalar collections and ignore the attractions of record-wise inserts and updates.

Associative Arrays
=============================================

Single row lookups in PL/SQL should never be performed as SQL statements (or cursors) inside a cursor loop. Instead, they should be merged into the main cursor using a table join; use an outer-join if there is a chance the row may not exist in the lookup table. This is still an efficient method even if the conditional PL/SQL logic does not need the joined
data for every row returned.

Some expert systems may have complex or iterative branching logic that makes the number of lookup permutations impractical. In these situations, the lookup tables can be cached in PL/SQL using an associative array (Index-By Table) so that lookups do not need a context switch.

DECLARE
TYPE currency_lkp_type IS TABLE OF currency%ROWTYPE
INDEX BY CURRENCY.CURRENCY_CODE%TYPE;
currency_lkp CURRENCY_LKP_TYPE;
BEGIN
FOR curr_rec IN (SELECT * FROM currency) LOOP
currency_lkp(curr.currency_code) := curr_rec;
END LOOP;

: :
FOR i IN txn_tab.FIRST .. txn_rec.LAST LOOP
: :
-- Lookup the currency table without using SQL

txn_tab.currency_desc := currency_lkp( txn_tab(i).currency_code );
: :
END LOOP;
END;

Note that this example breaks a cardinal rule by loading the associative array using an implicit-cursor FOR loop. It would be possible to BULK COLLECT the rows into a nested table, and then transfer the nested table row-by-row into the associative array, but it seems like overkill for downloading a small table just once for the entire job.

How to partition an existing table using re-definition

partition an existing table using re-definition
-----------------------------------------------------

1. CHECK PARTITIONED TABLE IS redefinition OR NOT

exec dbms_redefinition.can_redef_table('STLBAS', 'STTRNDTL_TEST');

2. CREATE interim TABLE (TEMPORARY TABLE) WITH PARTITION DESING
DROP MATERIALIZED VIEW STTRNDTL_TEST
DROP TABLE STTRNDTL_TEST_1
CREATE TABLE STTRNDTL_TEST_1
(
COMPCODE VARCHAR2(3 BYTE) ,
DOCTTYPE VARCHAR2(2 BYTE) ,
SUBTTYPE NUMBER(2) ,
DOCNUMBR VARCHAR2(8 BYTE) ,
DOCTDATE DATE ,
SERLNUMB NUMBER(5) ,
ACCTCODE VARCHAR2(10 BYTE) ,
DOCDUEDT DATE ,
COSTCODE VARCHAR2(10 BYTE) ,
SUBLEDCD VARCHAR2(6 BYTE) ,
TRANCODE VARCHAR2(3 BYTE) ,
NARATION VARCHAR2(200 BYTE) ,
DBCRCODE VARCHAR2(1 BYTE) ,
CURRCODE VARCHAR2(3 BYTE) ,
EXCGRATE NUMBER(13,9) ,
JVFCAMNT NUMBER(16,3) ,
JVLCAMNT NUMBER(16,3) ,
ORGAMTFC NUMBER(13,3) ,
ORGAMTLC NUMBER(13,3) ,
ADJAMTFC NUMBER(13,3) ,
ADJAMTLC NUMBER(13,3) ,
ORGBAKFC NUMBER(13,3) ,
ORGBAKLC NUMBER(13,3) ,
REFDOCTY VARCHAR2(3 BYTE) ,
REFDCSTY NUMBER(2) ,
REFDOCNO VARCHAR2(25 BYTE) ,
REFSRLNO NUMBER(6) ,
REFDOCDT DATE ,
REFDCNUM VARCHAR2(25 BYTE) ,
REFRDATE DATE,
INTDOCTY VARCHAR2(3 BYTE),
INTDCSTY NUMBER(2),
INTDOCNO VARCHAR2(8 BYTE),
INTDOCDT DATE,
CTRLMCRO VARCHAR2(2 BYTE),
STATCODE VARCHAR2(1 BYTE),
POSTDATE DATE,
PURGFLAG VARCHAR2(1 BYTE),
TRANSERL NUMBER(8),
ALLCFREZ VARCHAR2(1 BYTE),
LMODUSER VARCHAR2(6 BYTE),
LMODDATE DATE,
CLERFLAG VARCHAR2(1 BYTE),
OPRSTAMP VARCHAR2(6 BYTE),
TIMSTAMP DATE ,
AUTHBYID VARCHAR2(6 BYTE),
ORGRESBR VARCHAR2(3 BYTE),
AUTHRBID VARCHAR2(6 BYTE),
RESPDATE DATE,
INTSRLNO NUMBER(5),
NETWUSER VARCHAR2(30 BYTE),
AUTHFLAG VARCHAR2(1 BYTE)
)TABLESPACE ORBITS
Partition by range(DOCTDATE)
(
partition JAN2009 values less than (to_DATE('01-01-2009','DD-MM-RRRR')),
partition FEB2009 values less than (to_DATE('01-02-2009','DD-MM-RRRR')),
partition MAR2009 values less than (to_DATE('01-03-2009','DD-MM-RRRR')),
partition UPTOMAX values less than (maxvalue));


3. START REDEFINATION (TRANSFER DATA TO INTERIM TABLE FROM MAIN TABLE

--exec dbms_redefinition.start_redef_table('SCHEMA_NAME',
'MAIN_TABLE', 'interim TABLE');
exec dbms_redefinition.start_redef_table('STLBAS', 'STTRNDTL_TEST', 'STTRNDTL_TEST_1');

4. Copies the dependent objects of the original table onto the interim table

declare
error_count pls_integer := 0;
BEGIN
dbms_redefinition.copy_table_dependents('STLBAS', 'STTRNDTL_TEST', 'STTRNDTL_TEST_1',1, true, true, true, false,error_count);
dbms_output.put_line('errors := ' to_char(error_count));
END;
/

5. Completes the redefinition process (FOR COPYING THE ITERIM TABLE STRUCTURE TO MAIN TABLE)
exec dbms_redefinition.finish_redef_table('STLBAS', 'STTRNDTL_TEST', 'STTRNDTL_TEST_1');

6.Drop the interim table STTRNDTL_TEST_1
DROP TABLE STTRNDTL_TEST_1;

7 CHECK

Select * from user_tab_partitions ;

select index_name , status
from user_indexes
where table_name='STTRNDTL_TEST';

---------------------------------------------------------------

--- IF ERROR OCCURR LIKE
ORA-12091: cannot online redefine table "CR_2"."OUT_CDR" with materialized views
6. Remove the snapshot log and materialized view created during the unsuccessful
first execution of the DBMS_REDEFINITION.START_REDEF_TABLE procedure.
You can define the snapshot log and materialized view log of current table by

select log_table from user_snapshot_logs;
select master,log_table from user_mview_logs;

DROP MATERIALIZED VIEW LOG ON STTRNDTL_TEST;
or
exec dbms_redefinition.abort_redef_table('STLBAS', 'STTRNDTL_TEST', 'STTRNDTL_TEST_1');