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'));
Copyright © QY Network Company Ltd. All Rights Reserved. 2003-2018 群英 版权所有 茂名市群英网络有限公司
增值电信经营许可证 : B1.B2-20140078 粤ICP备09006778号-36 粤公网安备 44090202000006号 粤工商备P091701000595