哈希连接(hash join)原理 哈希连接(hashjoin) 访问次数：驱动表和被驱动表都只会访问0次或1次。 驱动表是否有顺序：有。 是否要排序：否。 应用场景： 1. 一个大表，一个小表的关联； 2. 表上没有索引； 3. 返回结果集比较大。 原理我们说的简单一点，先把驱
哈希连接(hash join)原理
哈希连接(hashjoin)
访问次数：驱动表和被驱动表都只会访问0次或1次。
驱动表是否有顺序：有。
是否要排序：否。
应用场景： 1. 一个大表，一个小表的关联；
2. 表上没有索引；
3. 返回结果集比较大。
原理我们说的简单一点，先把驱动表的关联字段hash到pga中（当然rowid也在pga中），然后扫描被驱动表，取第一条数据，将关联的字段hash 一下探测pga中的小表，如果匹配则关联，再取第二条........。
下面我们来做个试验：
sql> create table test1 as select * from dba_objects where rownum create table test2 as select * from dba_objects where rownum exec dbms_stats.gather_table_stats(user,'test1');sql> exec dbms_stats.gather_table_stats(user,'test2');sql> alter session set statistics_level=all;sql> select /*+leading(t1) use_hash(t2)*/count(*) from test1 t1, test2 t2 where t1.object_id = t2.object_id; count(*)---------- 100sql> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));plan_table_output-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------sql_id 3f2mts0kt82u2, child number 0-------------------------------------select /*+leading(t1) use_hash(t2)*/count(*) from test1 t1, test2 t2 where t1.object_id = t2.object_idplan hash value: 2544416891
----解释一下：
starts为该sql执行的次数。
e-rows为执行计划预计的行数。
a-rows为实际返回的行数。a-rows跟e-rows做比较，就可以确定哪一步执行计划出了问题。
a-time为每一步实际执行的时间（hh：mm：ss.ff），根据这一行可以知道该sql耗时在了哪个地方。
buffers为每一步实际执行的逻辑读或一致性读。
reads为物理读。
omem、1mem为执行所需的内存评估值，0mem为最优执行模式所需内存的评估值，1mem为one-pass模式所需内存的评估值。
0/1/m 为最优/one-pass/multipass执行的次数。
used-mem耗的内存
------------------------------------------------------------------------------------------------------------------| id | operation | name | starts | e-rows | a-rows | a-time | buffers | omem | 1mem | used-mem |------------------------------------------------------------------------------------------------------------------| 1 | sort aggregate | | 1 | 1 | 1 |00:00:00.01 | 19 | | | ||* 2 | hash join | | 1 | 100 | 100 |00:00:00.01 | 19 | 1066k| 1066k| 1162k (0)|| 3 | table access full| test1 | 1| 100 | 100 |00:00:00.01 | 4 | | | || 4 | table access full| test2 | 1 | 1000 | 1000 |00:00:00.01 | 15 | | | |------------------------------------------------------------------------------------------------------------------predicate information (identified by operation id):--------------------------------------------------- 2 - access(t1.object_id=t2.object_id)sql> select /*+leading(t1) use_hash (t2)*/count(*) from test1 t1, test2 t2 where t1.object_id = t2.object_id and t1.object_id = 99999; count(*)---------- 0sql> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));plan_table_output-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------sql_id f9zwsrs05kg0n, child number 0-------------------------------------select /*+leading(t1) use_hash (t2)*/count(*) from test1 t1, test2 t2 where t1.object_id =t2.object_id and t1.object_id = 99999plan hash value: 2544416891------------------------------------------------------------------------------------------------------------------| id | operation | name | starts | e-rows | a-rows | a-time | buffers | omem | 1mem | used-mem |------------------------------------------------------------------------------------------------------------------| 1 | sort aggregate | | 1 | 1 | 1 |00:00:00.01 | 4 | | | ||* 2 | hash join | | 1 | 1 | 0 |00:00:00.01 | 4 | 921k| 921k| 176k (0)||* 3 | table access full| test1 | 1 | 1 | 0 |00:00:00.01 | 4 | | | ||* 4 | table access full| test2 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |------------------------------------------------------------------------------------------------------------------predicate information (identified by operation id):--------------------------------------------------- 2 - access(t1.object_id=t2.object_id) 3 - filter(t1.object_id=99999) 4 - filter(t2.object_id=99999)sql> select /*+leading(t1) use_hash (t2)*/count(*) 2 from test1 t1, test2 t2 3 where t1.object_id = t2.object_id 4 and 1=2; count(*)---------- 0sql> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));plan_table_output-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------sql_id bnrfbt4ybxnnp, child number 0-------------------------------------select /*+leading(t1) use_hash (t2)*/count(*) from test1 t1, test2 t2 where t1.object_id =t2.object_id and 1=2plan hash value: 1013001923---------------------------------------------------------------------------------------------------------| id | operation | name | starts | e-rows | a-rows | a-time | omem | 1mem | used-mem |---------------------------------------------------------------------------------------------------------| 1 | sort aggregate | | 1 | 1 | 1 |00:00:00.01 | | | ||* 2 | filter | | 1 | | 0 |00:00:00.01 | | | ||* 3 | hash join | | 0 | 100 | 0 |00:00:00.01 | 921k| 921k| || 4 | table access full| test1 | 0 | 100 | 0 |00:00:00.01 | | | || 5 | table access full| test2 | 0 | 1000 | 0 |00:00:00.01 | | | |---------------------------------------------------------------------------------------------------------predicate information (identified by operation id):--------------------------------------------------- 2 - filter(null is not null) 3 - access(t1.object_id=t2.object_id)