You are responsible for a data warehouse application that uses records from an external table to update one of the dimension tables periodically. The records in the external table may contain data for new rows in the dimension table, or for updates to its existing rows.
Which type of SQL command would you use to transfer the data from the external table to the dimension table as efficiently as possible?
A. MERGE
B. SELECT ... CROSS JOIN
C. INSERT ALL ... SELECT
D. CREATE VIEW ... CONSTRAINT
Answer: A
Explanation:
To transfer the data from the external table to the dimension table as efficiently as possible you need to use the MERGE command. The MERGE command helps 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.
Incorrect Answers
B: The SELECT … CROSS JOIN command can be used to create a Cartesian product.
C: The INSERT ALL ... SELECT command will not help you in this case.
D: The CREATE VIEW ... CONSTRAINT cannot be used for purpose to insert new records or to update existing records in the dimension table.
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 DSS and Data-Warehouse Features
No comments:
Post a Comment