Examine the exhibit to view the query and its execution plan.
Identify the two correct interpretations that can be made from the execution plan.
Exhibit:
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.
Correct Answer: AB
Explanation/Reference:
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.
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
