Examine Exhibit 1 to view the query and its execution plan.
Examine Exhibit 2 to view the structure and indexes for the EMPLOYEES and DEPARTMENTS tables.
Examine Exhibit 3 to view the initialization parameters for the instance.
Why is sort-merge join chosen as the access method?
76_1 (exhibit):
76_2 (exhibit):
76_3 (exhibit):
A. Because the OPTIMIZER_MODE parameter is set to ALL_ROWS.
B. Because of an inequality condition.
C. Because the data is not sorted in the LAST_NAME column of the EMPLOYEES table
D. Because of the LIKE operator used in the query to filter out records
Correct Answer: A
Explanation/Reference:
Incorrect:
B: There is not aninequality conditionin the statement.
C: Merge joins are beneficial if the columns are sorted.
D:All regular joins should be able to use Hash or Sort Merge, except LIKE, !=, and NOT … joins.
Note:
*A sort merge join is a join optimization method where two tables are sorted and then joined.
*A "sort merge" join is performed by sorting the two data sets to be joined according to the join keys and then merging them together. The merge is very cheap, but the sort can be prohibitively expensive especially if the sort spills to disk. The cost of the sort can be lowered if one of the data sets can be accessed in sorted order via an index, although accessing a high proportion of blocks of a table via an index scan can also be very expensive in comparison to a full table scan.
*Sort merge joins are useful when the join condition between two tables is an inequality condition (but not a nonequality) like <, <=, >, or >=. Sort merge joins perform better than nested loop joins for large data sets. You cannot use hash joins unless there is an equality condition.
*When the Optimizer Uses Sort Merge Joins
The optimizer can choose a sort merge join over a hash join for joining large amounts of data if any of the following conditions are true:
/The join condition between two tables is not an equi-join.
/Because of sorts already required by other operations, the optimizer finds it is cheaper to use a sort merge than a hash join.
Reference: OracleDatabase Performance Tuning Guide,Sort Merge Joins
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
