Start Oracle trigger if updating then

Oracle trigger if updating then

We will pause the statistics between the two runs to reset the data. MERGE 2 INTO target_table tgt 3 USING source_table src 4 ON ( src.object_id = tgt.object_id ) 5 WHEN MATCHED 6 THEN 7 UPDATE 8 SET tgt.object_name = src.object_name 9 , tgt.object_type = src.object_type 10 WHEN NOT MATCHED 11 THEN 12 INSERT ( tgt.object_id 13 , tgt.object_name 14 , tgt.object_type ) 15 VALUES ( src.object_id 16 , src.object_name 17 , src.object_type ); We will now run a bulk update and insert as separate SQL statements.

It is probably no surprise that the MERGE was significantly faster than the PL/SQL solution.

We can see that MERGE performed less well than our two-part SQL solution; with it taking over twice as long. We can repeat the test against a typical PL/SQL-coded merge (common in older applications).

We will replace the two-part SQL solution with a PL/SQL loop that will attempt an update first and insert only if the update affects no rows.

It has been raised because Oracle found more than one target row that matched a single source row.