Parallel Update and Delete has some restriction on partitioned tables. The statement will just run in a parallel degree equal to the partition count.
I searched a solution to optimze a update on a 120G Table with about 300′000′000 rows which touches every row.
My current solution is a do a buld update within a pipelined function which is parallel executet.
With this solution I can run the update with bulk dml and in parallel.
Important is that the dml within the pipelined function is an anoymous transaction since any implicit or explicit commits are allowed in the session where the pipelined function is executet.
Now I have the possibility to tune any bulk batch size and any parallel degree to optimized the dml to my need.
CREATE OR REPLACE PACKAGE pgk_tom_mig_parallel_ptf_api AS
TYPE t_parallel_test_row IS RECORD ( TRANSACTIONID 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 t_transaction.TRANSACTIONID%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 pgk_tom_mig_parallel_ptf_api;
/
SHOW ERRORS
CREATE OR REPLACE PACKAGE BODY pgk_tom_mig_parallel_ptf_api 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 t_transaction.TRANSACTIONID%type;
BEGIN
LOOP
FETCH p_cursor BULK COLLECT INTO v_row1 LIMIT v_array_size;
FORALL i IN 1..v_row1.COUNT
UPDATE t_transaction t
SET t.yx =
.......
WHERE TRANSACTIONID=v_row1(i);
-- rollback;
commit;
pipe row (1);
EXIT WHEN p_cursor%NOTFOUND;
END LOOP;
end ptf_dml;
end pgk_tom_mig_parallel_ptf_api;
/
SHOW ERRORS
alter session force parallel query parallel 16;
select count(*) from table(pgk_tom_mig_parallel_ptf_api.ptf_dml( cursor(select /*+ PARALLEL(t,16) PARALLEL_INDEX(t, I_PK_TRANSID, 16) */ TRANSACTION
ID from T_TRANSACTION t ) ) );
alter session enable parallel query;
COMMIT;
