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?
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
Explanation:
Incorrect:
B: There is not an inequality condition in 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 ifany 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: Oracle Database Performance Tuning Guide , Sort Merge Joins
I’d say C.
No, A correct.
Question and answers are confusing.
For me,
B mought be correct but there is no inequality join between table. So i guess this answer is false or the statement in question are wrong typed.
C is not correct because when sort is required by another operation in execution plan, optimize could estimate it cheaper to use a sort merge join.
D is not correct because a “like” filter is not sufficient to favor a sort merge join.
So A is “correct” or the least bad
The optimizer may choose a sort merge join over a hash join for joining large amounts of data when any of the following conditions is true:
The join condition between two tables is not an equijoin, that is, uses an inequality condition such as <, , or >=.
In contrast to sort merges, hash joins require an equality condition.
Because of sorts required by other operations, the optimizer finds it cheaper to use a sort merge.
If an index exists, then the database can avoid sorting the first data set. However, the database always sorts the second data set, regardless of indexes.