Examine the exhibit to view the query and its execution plan.
Identify the two correct interpretations that can be made from the execution plan.
A.
The DEPT table is driving table and the EMP table join is the driven table.
B.
Rows from the DEPT table are first hashed by the join key into memory and then joined to the
EMP table on the join key.
C.
The EMP table is the driving table and the DEPT table us the driven table.
D.
The rows from the DEPT table are sorted first by the join key and then hashed into memory.
E.
Rows from both the tables are sorted by the join key, but only rows from the DEPT table are
hashed into memory.
Explanation:
Note:
* A hash join is performed by hashing one data set into memory based on join columns and
reading the other one and probing the hash table for matches. The hash join is very low cost when
the hash table can be held entirely in memory, with the total cost amounting to very little more than
the cost of reading the data sets. The cost rises if the hash table has to be spilled to disk in a onepass sort, and rises considerably for a multipass sort.
You should note that hash joins can only be used for equi-joins, but merge joins are more flexible.
In general, if you are joining large amounts of data in an equi-join then a hash join is going to be a
better bet.
* The ‘driving’ table is the table we will join FROM — that is JOIN TO other tables. For
example, lets say you have the query:
select * from emp, dept where emp.deptno = dept.deptno;
In this case the driving table might be DEPT, we would fetch rows from DEPT in a full
scan and then find the rows in EMP that match. DEPT is the driving table.
The answer is B,C
A, B are correct.
C is not right, because “The driving table is the first table joined, the one that will return the smallest number of rows, and hence, less “baggage” to be passed to later joins.”
b c are correct
For me these answers does not make much sense.
If hint was not used in the query DEPT would be the driving table because is smaller (4 rows, while EMP has 14 rows). But the hint tells optimizer to use EMP first, so as I understand it will build a hash table first on EMP and lookup DEPT table to join rows. So EMP is the driving table, DEPT is the driven table. If so “C” is correct.
But what about “B” that says something different!
D and E are incorrect.
In my opinion only one answer is correct.