New Features for Administrators - Question 079

Consider this syntax:

MERGE INTO tl
USING t2 ON (join predicate).....

What does the MERGE syntax do?

A. It performs a merge join of the row from T2 only if it doesn't exist in the T1 table.

B. It creates a natural join of tables T1 and T2 for all columns that have the same name.

C. It creates a Cartesian product of table T1 and table T2 for all columns that have the same name.

D. For each row from T2, it updates the row if it exists within table T1, otherwise it inserts the row into T1.

Answer: D

Explanation:

Oracle9i introduces the MERGE statement to enable you to retrieve rows from a source table and either update existing rows or insert new rows into a destination table. You update the row in the destination table if there is a matching row in the destination table; otherwise, you insert a new row into the destination table. You can specify the basis for the match with a condition in the ON clause. You specify the UPDATE in the WHEN MATCHED clause and you specify the INSERT in the WHEN NOT MATCHED clause.

Incorrect Answers

A: For each row from T2, it updates the row if it exists within table T1; otherwise it inserts the row into T1.

B: It does not create a natural join of tables T1 and T2.

C: It does not create a Cartesian product of table T1 and table T2.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 253 Chapter 5: Language Enhancements

Oracle 9i New Features, Robert Freeman, p. 122-123 Chapter 4: New Oracle9i Database DDS and Data-Warehouse Features