Oracle表连接操作——Hash Join（哈希连接）下

Hash Join是CBO优化器才能生成的执行计划操作，如果是选择了RBO就不能生成包括Hash Join的执行计划。此外，与Hash Join相关的Oracle参数还包括下面几个：

ü        Hash_Join_Enable

该参数是控制CBO启用Hash Join的开关。如果设置为True，则表示CBO可以使用Hash Join连接方式，否则就不可以使用。在目前的版本中，该参数已经演化为一个隐含参数，名称为“_hash_join_enable”。

SQL> col name for a20;

SQL> col value for a10;

SQL> col DESCRIB for a30;

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ

  2  FROM SYS.x$ksppi x, SYS.x$ksppcv y

  3  WHERE x.inst_id = USERENV (\'Instance\')

  4  AND y.inst_id = USERENV (\'Instance\')

  5  AND x.indx = y.indx

  6  AND x.ksppinm LIKE \'%hash_join_enable%\';

NAME                 VALUE      DESCRIB

-------------------- ---------- ------------------------------

_hash_join_enabled   TRUE       enable/disable hash join

该参数的隐式化，也就说明了CBO已经成熟到一定程度，Oracle官方不希望我们禁用掉这种Hash Join连接方式。当然，我们可以从system和session两层均可以暂时的禁用掉hash Join。

//此时_hash_join_enable=true

SQL> explain plan for select * from segs, tabs where segs.segment_name=tabs.table_name;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2106473715

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |   990 |   354K|    25   (4)| 00:00:01 |

|*  1 |  HASH JOIN         |      |   990 |   354K|    25   (4)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| TABS |   968 |   229K|    11   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| SEGS |  2267 |   274K|    13   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access(SEGS.SEGMENT_NAME=TABS.TABLE_NAME)

15 rows selected

//session层面禁用hash_join连接

SQL> alter session set _hash_join_enabled=false;

Session altered

NAME                 VALUE      DESCRIB

-------------------- ---------- ------------------------------

_hash_join_enabled   FALSE      enable/disable hash join

//相同的SQL，此时参数环境已经变化；

SQL> explain plan for select * from segs, tabs where segs.segment_name=tabs.table_name;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------

Plan hash value: 3475644097

------------------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |   990 |   354K|       |   144   (2)| 00:00:02 |

|   1 |  MERGE JOIN         |      |   990 |   354K|       |   144   (2)| 00:00:02 |

|   2 |   SORT JOIN         |      |   968 |   229K|   712K|    65   (2)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| TABS |   968 |   229K|       |    11   (0)| 00:00:01 |

|*  4 |   SORT JOIN         |      |  2267 |   274K|   824K|    79   (2)| 00:00:01 |

|   5 |    TABLE ACCESS FULL| SEGS |  2267 |   274K|       |    13   (0)| 00:00:01 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access(SEGS.SEGMENT_NAME=TABS.TABLE_NAME)

       filter(SEGS.SEGMENT_NAME=TABS.TABLE_NAME)

已选择18行。

可见，当我们session级别禁用了hash Join连接之后，CBO不能进行Hash Join路径选择。于是选择了Merge Join路径，显然无论是执行时间还是CPU成本，Merge Join略逊一筹。

ü        Hash_Area_Size

Hash Join操作是依赖独立的私有空间，我们称之为Hash_Area。Hash Area在Join过程中的作用就是将连接小表尽可能的缓存在Hash Area中，供进行Hash匹配和Bucket内部精确匹配。Hash Area是贮存在PGA中，属于会话session独立的一块空间。如果Hash Area较小，不足以存放小表全部数据，就会引起Temp表空间的使用，进而影响Hash Join性能。

SQL> show parameter hash

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

hash_area_size                       integer     131072

因为每一个会话都会开启一个Hash Area进行Hash 操作，所以通常Hash Area的大小不会设置很大。与Hash Area类似的空间是Sort Area，用于进行SQL语句中的Order by操作，也是一个依赖分配的参数项目。通常，Hash Area被分配大小为Sort Area的两倍。

SQL> show parameter sort_area

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sort_area_retained_size              integer     0

sort_area_size                       integer     65536

进入Oracle 9i之后，特别是10g出现，Oracle共享内存和独占内存分配策略呈现自动化和自适应化的趋势，而且这种技术也逐渐成熟。DBA只需要确定Oracle数据库总的内存使用大小（memory_target），就会根据算法、负载不断调整实现自适应的内存分区调整。

作为PGA分配，Oracle推出的自动调控参数是pga_aggregate_target，表示所有会话的PGA总分配大小。如果不启用PGA自动分配，该参数值就是设置为0。

SQL> show parameter pga

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target                 big integer 0

ü        Hash_multiblock_io_count

该参数表示在进行Hash Join连接操作的时候，一次可以读取的块个数。在最新的版本中，该参数已经变成了一个隐含参数。

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ

  2  FROM SYS.x$ksppi x, SYS.x$ksppcv y

  3  WHERE x.inst_id = USERENV (\'Instance\')

  4  AND y.inst_id = USERENV (\'Instance\')

  5  AND x.indx = y.indx

  6  AND x.ksppinm LIKE \'%hash_multiblock%\';

NAME                           VALUE      DESCRIB

------------------------------ ---------- ------------------------------

_hash_multiblock_io_count      0          number of blocks hash join wil

                                          l read/write at once

这个参数可以追溯到Oracle 8i时代，当时设置的默认值为1。在以后的版本中，通常设置为0。这个参数对IO影响重大，不同的硬件环境、系统负载下效果不同。所以，当设置为0的时候，Oracle是会每次自动计算该值。

作为我们来讲，最好不要进行该参数的设置。

4、连接三模式