Script - DML Paralelo (pipelined funcition + bulk DML)

Extraído de : http://www.asktherealtom.ch/?p=94

-- Tabla de ejemplo
CREATE TABLE TRANSACTION(TRANSACTION_ID NUMBER, PRUEBA VARCHAR2(100)) TABLESPACE ...;
CREATE INDEX I_PK_TRANSID ON TRANSACTION(TRANSACTION_ID) TABLESPACE ...;
-- Rellenamos la tabla
INSERT INTO TRANSACTION SELECT ... FROM ... WHERE ROWNUM <=100000;


CREATE OR REPLACE PACKAGE PKG_PARALLEL AS

TYPE t_parallel_test_row IS RECORD ( TRANSACTION_ID NUMBER(16));
TYPE t_parallel_test_ref_cursor IS REF CURSOR RETURN t_parallel_test_row;
TYPE t_dml_counter IS TABLE OF NUMBER;

TYPE t_TRANSACTIONID_tab IS TABLE OF transaction.TRANSACTION_ID%TYPE INDEX BY BINARY_INTEGER;
v_row1 t_TRANSACTIONID_tab;
v_array_size PLS_INTEGER := 500000;

function ptf_dml(p_cursor IN t_parallel_test_ref_cursor)
return t_dml_counter
pipelined
parallel_enable (PARTITION p_cursor BY ANY );
--parallel_enable (PARTITION p_cursor BY RANGE (TRANSACTIONID));

END PKG_PARALLEL;
/
SHOW ERRORS
CREATE OR REPLACE PACKAGE BODY PKG_PARALLEL AS

--create or replace function ptf_dml(p_cursor IN SYS_REFCURSOR)
function ptf_dml(p_cursor IN t_parallel_test_ref_cursor)
return t_dml_counter
pipelined
-- parallel_enable (PARTITION p_cursor BY RANGE (TRANSACTIONID))
parallel_enable (PARTITION p_cursor BY ANY )
is
PRAGMA AUTONOMOUS_TRANSACTION;
r_rowid transaction.TRANSACTION_ID%type;
BEGIN
LOOP
FETCH p_cursor BULK COLLECT INTO v_row1 LIMIT v_array_size;
FORALL i IN 1..v_row1.COUNT

UPDATE transaction t
SET t.PRUEBA = T.PRUEBA||'A'
WHERE TRANSACTION_ID=v_row1(i);

-- rollback;
commit;
pipe row (1);
EXIT WHEN p_cursor%NOTFOUND;
END LOOP;
end ptf_dml;

end PKG_PARALLEL;
/
SHOW ERRORS

alter session force parallel query parallel 4;
select count(*) from table(PKG_PARALLEL.ptf_dml( cursor(select /*+ PARALLEL(t,4) PARALLEL_INDEX(t, I_PK_TRANSID, 4) */ TRANSACTION_ID from TRANSACTION t ) ) );