Examine the exhibit to view the query and its execution plan. Identify the two correct interpretations that can be made from the execution plan.

Question

Exhibit

Options

• AThe DEPT table is driving table and the EMP table join is the driven table.
• BRows from the DEPT table are first hashed by the join key into memory and then joined to the EMP table
• CThe EMP table is the driving table and the DEPT table us the driven table.
• DThe rows from the DEPT table are sorted first by the join key and then hashed into memory.
• ERows from both the tables are sorted by the join key, but only rows from the DEPT table are hashed into

• A
  74% (26)
• C
  3% (1)
• D
  9% (3)
• E
  14% (5)

Explanation

• 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 one-pass 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 * 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.

No community discussion yet for this question.