Oracle triggers if updating performance

10-Sep-2015 02:17 by 5 Comments

Oracle triggers if updating performance - Free Online

We can see that this is the same as the MERGE count.MERGE is useful for combining larger source and target datasets, particularly for slowly-changing dimensions in data warehouses.

The following output is an Autotrace explain plan for our original MERGE statement from above.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.In versions prior to 9i, we would have to code this scenario either in separate bulk SQL statements or in PL/SQL.We will compare MERGE to these methods later in this article.Before we begin, we will create two scratch tables to work with; a source table and a target table.

We will start by creating the source table as a selection of ALL_OBJECTS data. In the following example, we will merge the source table into the target table.

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.

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.

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.

  1. Free web chat no signup 24-Mar-2016 17:39

    Find out who is single near you and start dating locally! Me and view a list of singles who live near you and are looking to date. Sign-up today for free and search your local area for great singles.