Validating event net
31-Jul-2017 19:05' rows updated.' ); 42 43 END; 44 / ===================== PARSING IN CURSOR #40 len=100 dep=1 uid=57 oct=6 lid=57 tim=87989151677 hv=2983097369 ad='20e931b4' UPDATE UPDATE_TABLE SET ID = : B1 , NAME = : B2 , TYPE = : B3 , OWNER = : B4 , CREATED = : B5 WHERE ID = : B1 END OF STMT ===================== This trace file clearly highlights the problem with SET ROW.
*/ 19 OPEN c_source; 20 FETCH c_source BULK COLLECT INTO aa_rows; 21 CLOSE c_source; 22 23 /* Process batch of data... */ 31 aa_ids(i) := aa_rows(i).id; 32 33 END LOOP; 34 35 /* FORALL update using SET ROW. We have updated the primary key ID column (even though we did not modify it).FORALL update using SET ROW and ROWIDs. First, the predicate is outside the in-line view, which avoids the ORA-03113 issue.Second, ROWID is a pseudo-column so does not need to be projected in the in-line view to be referenced outside.We will assume that the sample table has been partially loaded and 2 of the 5 columns in each record must be updated by some "post-processing" (this potentially expensive technique is surprisingly quite common in many ETL systems).
We suppose that these 2 columns must undergo some complex transformations that make this operation too complicated for a single SQL UPDATE. Again, to keep it simple, we will bulk fetch our data in one pass, rather than use LOOP and LIMIT (an absolute necessity when dealing with large volumes of data).
Not only do we update the primary key (which will generate locks on child tables) but we also generate more undo and redo than is necessary, thereby reducing the efficiency, concurrency and overall scalability of our system.