The second also uses the MERGE statement, but in a row-by-row manner. The first uses the straight MERGE statement. The following code compares the performance of four merge operations. SELECT object_id, owner, object_name, object_typeĬONSTRAINT source_tab_pk PRIMARY KEY (object_id)ĮXEC DBMS_STATS.gather_table_stats(USER, 'dest_tab', cascade=> TRUE) The source table contains all the rows from the ALL_OBJECTS view, while the destination table contains approximately half of the rows. The MERGE statement is optimized for merging sets of data, rather than single rows, as shown in the example below.Ĭreate the following test tables. USING (SELECT * FROM hr_records WHERE start_date > ADD_MONTHS(SYSDATE, -1)) h MERGE Statement Enhancements in Oracle Database 10gĬonsider the following example where data from the HR_RECORDS table is merged into the EMPLOYEES table.The MERGE statement reduces table scans and can perform the operation in parallel if required. The MERGE statement was introduced in Oracle 9i to conditionally insert or update data depending on its presence, a process also known as an 'upsert'. Home » Articles » 9i » Here MERGE Statement

Toad for oracle student