sql hash join VS nested loop



最近看了Expert Oracle SQL Optimization,Deployment, and Statistics这本书，其中介绍了hash join的优缺点，这里做个笔记。

Hash joins have the following advantages over nested loops when the probe row source is a table:
•Every block in the probe table is visited at most once and not potentially multiple times as with a nested loop.
•No index is required on the join column in the probe table.
•If a full table scan (or fast full index scan) is used for accessing the probe table then multi-block reads can be used, which are much more efficient than single-block reads through an index.
•Join inputs can be swapped.
However, hash joins have the following disadvantages:
•If a block in the probe table contains no rows that match any of the rows in the driving row source it may still be visited. So, for example, if the size of the probe table was 1TB, there was no selection predicate, and only two rows matched the join predicates, we would scan the whole 1TB table rather than picking out two rows through an index.
•While both join operands are small, hash joins scale linearly as nested loops do. However, if the driving row source gets too big the hash table will spill onto disk, ruining the linear performance properties.
•Hash joins can only be used with equality join predicates.

SELECT /*+
leading(D)
use_hash(E)
*/
 e.*, d.loc
  FROM scott.emp e, scott.dept d
 WHERE hiredate > DATE '1980-12-17'
   AND e.deptno = d.deptno;

SELECT *
  FROM TABLE(DBMS_XPLAN.display_cursor(format => 'ALL +IOSTATS LAST'));

Plan hash value: 615168685

---------------------------------------------------------------------------------------------------------------------
| Id      | Operation                                  | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|    0   | SELECT STATEMENT              |      |      1 |        |       |     6 (100)|          |     13 |00:00:00.01 |      15 |
|*   1   |  HASH JOIN                               |      |      1 |     13 |  1404 |     6   (0)| 00:00:01 |     13 |00:00:00.01 |      15 |
|     2  |   TABLE ACCESS FULL| DEPT |      1 |      4 |    84 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       7 |
|*   3   |   TABLE ACCESS FULL| EMP  |      1 |     13 |  1131 |     3   (0)| 00:00:01 |     13 |00:00:00.01 |       8 |
---------------------------------------------------------------------------------------------------------------------

SELECT /*+
leading(D)
use_nl(E)
*/
 e.*, d.loc
  FROM scott.emp e, scott.dept d
 WHERE hiredate > DATE '1980-12-17'
   AND e.deptno = d.deptno;

SELECT *
  FROM TABLE(DBMS_XPLAN.display_cursor(format => 'ALL +IOSTATS LAST'));

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |    10 (100)|          |     13 |00:00:00.01 |      37 |
|   1 |  NESTED LOOPS          |      |      1 |     13 |  1404 |    10   (0)| 00:00:01 |     13 |00:00:00.01 |      37 |
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |    84 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       8 |
|*  3 |   TABLE ACCESS FULL| EMP  |      4 |      3 |   261 |     2   (0)| 00:00:01 |     13 |00:00:00.01 |      29 |
---------------------------------------------------------------------------------------------------------------------