场景：

A，B 两个表的大小都是超过50G的，现在需要对这两个表进行Hash连接，SQL如下：

select * from A,B where A.id = b.idhttp://www.cndba.cn/cndba/arealman/article/238

显然，这个问题的性能瓶颈在于PGA中无法完全容纳build hash table时需要的内存而将两表hash分区到TEMP表空间，后续频繁产生的读写临时表空间。

有什么优化方案吗

落落的博客说需要加一个伪列，对这个伪列进行 Ora_Hash() 函数计算后，作为 LIST 分区分割的条件分区http://www.cndba.cn/cndba/arealman/article/238

select * from A,B where A.id = B.id and A.hash_col = B.hash_col;

如此就可以将两表分割为多个小块，相同hash值的小块两两hash join,巧妙避开了上面的性能瓶颈。

实际上不需要处理的那么复杂，可直接将连接列作为分割条件做Hash分区，效果是一样的。当然，两个表的分区数目必须要一致，以保证左右两个表的同一连接键值计算到的分区值是一样的。

测试脚本：

1.MV_NODE1 / MV_NODE2 两个巨表的建表语句

create table MV_NODE1
(
  RD_0          ROWID not null,
  NODEID         VARCHAR2(128),
  LABEL_CN        VARCHAR2(128),
  ......
)
PARTITION BY  HASH (nodeid) PARTITIONS 64;

B 表也一样

对两表做数据做数据：
insert into mv_node1
select * from mv_node;
insert into mv_node2
select * from mv_node;
commit;

执行结果如下：http://www.cndba.cn/cndba/arealman/article/238

可以看到，其实已经做了这个动作。

PS: PARTITION HASH ALL 如果是检索单个哈希分区表，这个动作一般是作为TABLE ACCESS FULL的父操作，表示将子操作扫描的哈希分区结果合并。则合并分区结果。如果发生在HASH JOIN的父节点，则表示合并各个哈希分区两两HASH JOIN结果。

那么再来一个实验，如果左右两个表的分区数不一致，会发生什么结果呢：

--再创建一个哈希分区表，不过这次是分为32个分区：

create table MV_NODE3
(
  RD_0          ROWID not null,
  NODEID         VARCHAR2(128),
  LABEL_CN        VARCHAR2(128),
  ......
)
PARTITION BY  HASH (nodeid) PARTITIONS 32;

对两表做数据做数据：
insert into mv_node3
select * from mv_node;
commit;

看看结果：

从执行计划可以看到，是先将 mv_node1，mv_node3各自的分区合并后，再进行hash join的，这样的话就与两个大表直接hash join没什么差别了。属于最差的hash join。

不过这里在join之前，使用了布隆过滤（oracle的版本为11.2.0.4），有点意思。。。从实际数据来看，布隆过滤应该是错误的选择，因为mv_node1、mv_node3的数据是一样的，并没有起到过滤的作用，反而增加了CPU的负担。

PS：经过在生产环境中测试，前后的两种方案的性能差别巨大，后者执行完需要5+个小时，而前者则大概需要40分钟。生产环境的IO性能其实比较一般，平均的IO也就100M/S,这40分钟其实可以估算到大部分都消耗在IO上的。好一点的服务器，其执行时间可以更短。http://www.cndba.cn/cndba/arealman/article/238

http://www.cndba.cn/cndba/arealman/article/238

版权声明：本文为博主原创文章，未经博主允许不得转载。

hash join 布隆过滤