oracle表连接的优化

多表连接的三种方式： HASH JOIN，MERGE JOIN，NESTED LOOP

NESTED LOOP： 嵌套循环连接，适用于内表数据量较小时。外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于1 万不适合），而且在内表的连接字段上最好建立索引。

HASH JOIN： 哈希/散列连接，适用于小表（驱动表）连大表，且较小的表完全可以放于内存中的情况。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O 的性能。

SORT MERGE JOIN： 排序合并连接，适用于没有索引且数据已经排序、不等价关联等情况。通常情况下散列连接的效果都比排序合并连接要好，然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。

一、 大表关联的优化方法 -- hash分区

参考链接：20亿与20亿表关联优化方法(超级大表与超级大表join优化方法)

问题：两个20亿大表关联耗时一天一夜

执行的SQL： select * from t1,t2 where t1.object_id=t2.object_id;

关联慢的原因：单个进程的PGA装不下大表数据 --> 消耗大量临时表空间 --> 临时表来回读写

优化方法：

1）开并行，并行hash。 -- 不可行

2）利用MPP架构/HADOOP架构思想：数据分割。

数据分割方法：一个是分区；另外一个是分表。本文选用分区。

步骤：

1）创建表P1，P2。在T1的表结构基础上多加一个字段HASH_VALUE，而且依据HASH_VALUE进行LIST分区。同理，P2。

注意：工作中具体需要多少分区需自己判断，但P1和P2表的分区必须一模一样。 

CREATE TABLE P1(
HASH_VALUE NUMBER,
OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(128),
SUBOBJECT_NAME VARCHAR2(30), 
OBJECT_ID NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(19),
CREATED DATE,
LAST_DDL_TIME DATE,
TIMESTAMP VARCHAR2(19),
STATUS VARCHAR2(7),
TEMPORARY VARCHAR2(1),
GENERATED VARCHAR2(1),
SECONDARY VARCHAR2(1),
NAMESPACE NUMBER,
EDITION_NAME VARCHAR2(30)
)   
   PARTITION BY  list(HASH_VALUE)
(
partition p0 values (0),
partition p1 values (1),
partition p2 values (2),
partition p3 values (3),
partition p4 values (4)
)

 2）向P1，P2中插入数据。

oracle中的hash分区就是利用的ora_hash函数。

partition by hash(object_id)  <=> ora_hash(object_id,4294967295)

ora_hash(列,hash桶) ，hash桶默认是4294967295，能够设置0到4294967295。

ora_hash(object_id,4) 会把object_id的值进行hash运算，然后放到 0,1,2,3,4 这些桶里面。

delete t1 where object_id is null;
commit;

delete t2 where object_id is null;
commit;

insert into p1
select ora_hash(object_id,4), a.*  from t1 a;  ---工作中用append parallel并行插入
commit;

insert into p2
select ora_hash(object_id,4), a.*  from t2 a;  ---工作中用append parallel并行插入
commit;

3）执行表关联。

优化后执行时间大约1小时。可将该过程整理为存储过程。

select * from p1,p2 where p1.object_id=p2.object_id and p1.hash_value=0 and p2.hash_value=0;
select * from p1,p2 where p1.object_id=p2.object_id and p1.hash_value=1 and p2.hash_value=1;
select * from p1,p2 where p1.object_id=p2.object_id and p1.hash_value=2 and p2.hash_value=2;
select * from p1,p2 where p1.object_id=p2.object_id and p1.hash_value=3 and p2.hash_value=3;
select * from p1,p2 where p1.object_id=p2.object_id and p1.hash_value=4 and p2.hash_value=4;

 二、小表与大表关联 -- hash join

参考链接：oracle表连接----->哈希连接(Hash Join)

对Hash Join的一次优化

1）执行hash join

select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id;  --可连个表都建立hash,  use_hash(t1 t2)

2）查看执行计划

如果条件成立，驱动表和被驱动表都只被访问1次（主要看 starts），否则访问0次。

哈希连接中驱动表的选择非常重要，性能（主要看 A-Time  | Buffers |  OMem |  1Mem | Used-Mem）差别也大。一般选用结果集较小的表（非数据量）为驱动表。

-- 查看执行代码的 sql_id
select sql_id, child_number, sql_text from v$sql where sql_text like '%use_hash(t2)%';

-- 查看指定 sql_id 的执行计划
select * from table(dbms_xplan.display_cursor('036fyatp73h9n',0,'allstats last'));

3）其他

hash join 不支持不等值连接。（执行计划走的是 NESTED LOOPS JOIN）

explain plan for select /*+ leading(t2) use_hash(t1)*/ * from t1,t2 where t1.id<>t2.t1_id and t1.num=20;

select * from table(dbms_xplan.display);

　　

　　参考链接：

https://zhidao.baidu.com/question/453003972.html