Oracle if updating trigger

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.It has been raised because Oracle found more than one target row that matched a single source row.MERGE will allow multiple updates of a single target row, however, as long as the join is key-preserved.Execution Plan ---------------------------------------------------------- 0 MERGE STATEMENT Optimizer=CHOOSE (Cost=194 Card=86889 Bytes=9557790) 1 0 MERGE OF 'TARGET_TABLE' 2 1 VIEW 3 2 HASH JOIN (OUTER) (Cost=194 Card=86889 Bytes=7038009) 4 3 TABLE ACCESS (FULL) OF 'SOURCE_TABLE' (Cost=46 Card=86889 Bytes=2867337) 5 3 TABLE ACCESS (FULL) OF 'TARGET_TABLE' (Cost=24 Card=18950 Bytes=909600) We can see that Oracle performs an outer join between the source dataset and target table (in our case we happen to have a hash join).This creates a non-mergeable view (this is an unfortunate coincidence in terminology) that is applied back to the target table.

In the following example, we will use a variation of Tom Kyte's RUNSTATS utility to compare our original MERGE with a solution that runs a bulk update followed by a bulk insert.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.If we explain a MERGE statement, we can see the mechanism Oracle uses to determine whether source and target rows match.The following output is an Autotrace explain plan for our original MERGE statement from above.Without the outer join, Oracle would need to implement a "two-pass" solution such as we might code ourselves with a separate INSERT and UPDATE statement.

You must have an account to comment. Please register or login here!