I am trying to update one table from onther table through DBMS_PARALLEL_EXECUTE .
My source and target tables both exists on same schema and I have created a procedure with DBMS_PARALLEL_EXECUTE on different schema. Chunkc(By row_id) has created on Staging table.All othrs steps like create task etc.. has already done and below update is a dynamic sql . I have created chunk on STG table .
UPDATE MAIN_TAB TGT
SET (C1,C2,C3) = (
SELECT C1,C2,C3
ROM STG SRC
WHERE SRC.PRIMARY_KEY =TGT. PRIMARY_KEY
)
WHERE ROWID BETWEEN :START_ID AND :END_ID;
This update doesn’t work if chunk is created on staging table. If I create chunk on MAIN_TAB which is target then above update works but this not logically correct. i.e. chunk should be created on SRC table whic is STG table here and update should happened based on primary key and rowid .
Or
UPDATE MAIN_TAB TGT
SET (C1,C2,C3) = (
SELECT C1,C2,C3
FROM STG SRC
WHERE SRC.PRIMARY_KEY =TGT. PRIMARY_KEY
AND ROWID BETWEEN :START_ID AND :END_ID
);
this update giving me error “ORA-14402: updating partition key column would cause a partition change” but same update is working if I use it through bulk update with parallel DML. Actually I am comparing performance between bulk update and DBMS_PARALLEL_EXECUTE.