Rowsource not updating are blake and sierra from versaemerge dating
Cached Commit SCN refere 286 56,854 56,568 STAT..table scan blocks gotten 951 86,889 85,938 STAT..buffer is not pinned cou 975 86,924 85,949 STAT.block changes 175,740 262,562 86,822 STAT..index fetch by key 0 86,891 86,891 STAT.work - consistent rea 967 129,991 129,024 STAT..execute count 13 130,338 130,325 STAT..calls to get snapshot sc 28 130,355 130,327 STAT..consistent gets - examin 230 130,962 130,732 STAT..recursive calls 567 218,074 217,507 STAT..consistent gets 1,469 260,988 259,519 LATCH.library cache pin 119 261,164 261,045 LATCH.library cache 166 261,675 261,509 STAT..session logical reads 134,411 438,136 303,725 LATCH.cache buffers chains 579,034 1,190,456 611,422 STAT.size 23,372,796 32,740,676 9,367,880 Run1 latches total versus run2 -- difference and pct Run1 Run2 Diff Pct 687,702 1,889,744 1,202,042 36.39% PL/SQL procedure successfully completed.
It is probably no surprise that the MERGE was significantly faster than the PL/SQL solution.
We could speed up the latter by using bulk processing, but we wouldn't be able to achieve a reduction of two-thirds required to match the MERGE. This means that for each source row, Oracle needs to be able to identify a single target record for update.
The simplest method of ensuring that the MERGE is key-preserved is to join source and target according to the primary key of the target.
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_type = src.object_type 9 , tgt.object_name = src.object_name 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 ); This simply means that a single target row might be updated multiple times, which could give rise to some data quality issues (for example, if compound arithmetic such as summing is included in the update of some columns).
MERGE can be used with in PL/SQL (even row-by-row) but should generally be considered a set operation.
The sqlplus feedback gives us the breakdown of the previous merge rowcount.
MERGE is useful for combining larger source and target datasets, particularly for slowly-changing dimensions in data warehouses.We will capture a count of the target table rows before and after the merge.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 ); Note that sqlplus reports the number of rows merged. Oracle treats MERGE as a MERGE and not an UPDATE INSERT statement. As a rough sanity check, we can report the record count in the target table following the MERGE.This article introduces the new MERGE SQL command (sometimes referred to as "UPSERT").MERGE is a DML command that enables us to optionally update or insert data into a target table, depending on whether matching records already exist.