表的三大连接之 hash join

hash join中t2表只会被访问1次或者0次(驱动表被访问1次，被驱动表被访问1次)

set linesize 1000
select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

ZBB@test>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  3uqx8rxjq0t44, child number 0
-------------------------------------
select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id

Plan hash value: 1838229974

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     99 |00:00:00.05 |    1019 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |     99 |     99 |00:00:00.05 |    1019 |   960K|   960K| 1253K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |     99 |     99 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |    111K|  99999 |00:00:00.01 |    1012 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."ID"="T2"."T1_ID")

Note
-----

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
   - dynamic sampling used for this statement (level=2)


24 rows selected.

hash join中t2表被访问0次的情况

select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id and t1.n=99999999999;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
ZBB@test>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID  9yq0rd42w3rbc, child number 0
-------------------------------------
select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id
and t1.n=99999999999

Plan hash value: 1838229974

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      0 |00:00:00.01 |       7 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |      1 |      0 |00:00:00.01 |       7 |   683K|   683K|  186K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      0 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      0 |    111K|      0 |00:00:00.01 |       0 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."ID"="T2"."T1_ID")
   2 - filter("T1"."N"=99999999999)


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)


26 rows selected.

hash john中t1和t2都被访问0次的情况

select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id and 1=2;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
ZBB@test>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID  4wtwy87qctwpw, child number 0
-------------------------------------
select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id
and 1=2

Plan hash value: 487071653

-------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      0 |00:00:00.01 |       |       |          |
|*  1 |  FILTER             |      |      1 |        |      0 |00:00:00.01 |       |       |          |
|*  2 |   HASH JOIN         |      |      0 |     99 |      0 |00:00:00.01 |   740K|   740K|          |
|   3 |    TABLE ACCESS FULL| T1   |      0 |     99 |      0 |00:00:00.01 |       |       |          |
|   4 |    TABLE ACCESS FULL| T2   |      0 |    111K|      0 |00:00:00.01 |       |       |          |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)
   2 - access("T1"."ID"="T2"."T1_ID")

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


27 rows selected.

总结： hash join中，驱动表被访问0次或者1次，被驱动表也是被访问0次或者1次。绝大多数情况下是驱动表和非驱动表各被访问1次。

END.