Oracle表连接之哈希连接
Oracle Hash join 是一种非常高效的join 算法,主要以CPU(hash计算)和内存空间(创建hash table)为代价获得最大的效率。Hash join一般用于大表和小表之间的连接,我们将小表构建到内存中,称为Hash cluster,大表称为probe表。
当两个表做hash join时,oracle会选择一个表作为驱动表,先根据过滤条件排除不必要的数据,然后将结果集做成hash表,放入进程的hash
area,接着扫描第二张表,将记录的join字段值做hash运算,到内存的hash表里面去探测,如果探测成功,就返回数据,否则这行就丢弃掉。
(miki西游 @mikixiyou 原文链接: http://mikixiyou.iteye.com/blog/1709321
)
select /*+use_nl(a b)*/ a.*,b.*
from dba_obj a,all_obj b
where a.object_id=b.object_id
and a.object_name like 'tt%'
SELECT STATEMENT, GOAL = ALL_ROWS
NESTED LOOPS
TABLE ACCESS FULLSCOTTDBA_OBJ
TABLE ACCESS BY INDEX ROWIDSCOTTALL_OBJ
INDEX UNIQUE SCANSCOTTPK_ALL_OBJ
执行计划解读
两个表使用了嵌套循环连接。首先访问dba_obj表,得到全部记录。然后按照此表记录依次去扫描all_obj表,扫描过程走索引快速得到all_obj的记录。
select /*+use_hash(a b)*/ a.*,b.*
from dba_obj a,all_obj b
where a.object_id=b.object_id
and a.object_name like 'tt%'
SELECT STATEMENT, GOAL = ALL_ROWS
HASH JOIN
TABLE ACCESS FULLSCOTTDBA_OBJ
TABLE ACCESS FULLSCOTTALL_OBJ
执行计划解读
两个表使用了哈希连接。首先访问dba_obj表,得到全部记录,进行hash运算,放到内存hash area中形成hash table,也称为hash cluster。
然后,再腾出手来,全面扫描all_obj表,每扫描到一条记录时,将join字段进行hash运算,然后到hash area中去找与dba_obj表匹配的记录。
这个行为称为probe,中文称探测。此表也称为probe表。
hash table表是保存在hash area内存区域中,而这个区域在oracle中是分配在pga中。
PGA 包括 进程内存、UGA、sort area,bitmap merge area和hash area。UGA包含session状态信息和private sql area。
使用这个10104 event可以分析hash area的内存分配情况。
alter system set events '10104 trace name context forever,level 2';
select count(*)
from (select /*+use_hash(i g) leading(i)*/
i.*, g.*
from tdividenddetail i, tproductinfo g
where i.c_fundcode = g.fundcode
and i.d_cdate > sysdate - 100);
使用use_hash提示强制让两个表采用hash join关联,然后使用leading提