优化hash连接

HASH连接需要两个表只扫描一遍，针对连接字段做Hash连接后返回用户所需数据，对于大表而言PGA很可能成为瓶颈，走TEMP空间会拖慢SQL的执行，如果无法调整表连接方式，就需要其他方式做优化，这里我们提供一个Hash的优化思路。

1 —使用并行以及指定广播表优化hash连接

小表a 和大表b做hash如何优化

create table a as select * from all_objects where object_id<101;

create table b as select * from all_objects ;

默认执行计划

explain plan for select * from a ,b where a.object_id=b.object_id;

select * from table(dbms_xplan.display);

开并行

explain plan for select /*+ parallel(2) */ * from a ,b where a.object_id=b.object_id;

select * from table(dbms_xplan.display);

指定hash连接，开并行，小表向大表广播

explain plan for select /*+ parallel(2) use_hash(a ,b)  pq_distribute(a none,broadcast)  */ * from a ,b where a.object_id=b.object_id;

select * from table(dbms_xplan.display);

2 hash 大表a 和大表 b  连接

使用hash-hash的方式，将两个大表根据并行度分别hash，再做hash连接

explain plan for select /*+ parallel(2) use_hash(a ,b)  pq_distribute(b hash,hash)  */ * from a ,b where a.object_id=b.object_id;

select * from table(dbms_xplan.display);

alter session set statistics_level=all;

set line 200 pagesize 200;

 select /*+ parallel(2) use_hash(a ,b)  pq_distribute(b hash,hash)  */ * from a ,b where a.object_id=b.object_id;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));