Hash join优化要点

要点1：两表的连接条件建立索引

构造环境

-环境构造
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS PURGE;
CREATE TABLE t1 (
     id NUMBER NOT NULL,
     n NUMBER,
     contents VARCHAR2(4000)
   )
   ;
CREATE TABLE t2 (
     id NUMBER NOT NULL,
     t1_id NUMBER NOT NULL,
     n NUMBER,
     contents VARCHAR2(4000)
   )
   ;
execute dbms_random.seed(0);
INSERT INTO t1
     SELECT rownum, rownum, dbms_random.string('a', 50)
       FROM dual
     CONNECT BY level <= 10000
      ORDER BY dbms_random.random;
INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000
    ORDER BY dbms_random.random;
COMMIT;
select count(*) from t1;
select count(*) from t2;

实验1：测试两表都没有索引的情况

--首先测试Hash Join两表的限制条件皆无索引的情况

alter session set statistics_level=all ;
set linesize 1000
SELECT /*+ leading(t2) use_hash(t1)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id
and t1.n=19
and t2.n=12;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Hash join优化要点

实验二：在t1表的连接条件建立索引

create index idx_t1_n on t1(n);

Hash join优化要点

结论：从Buffer中可以看到，从1104到了1008，性能略有提升

实验三：在t2表的连接条件建立索引。

create index idx_t2_n on t2(n);

Hash join优化要点

要点2：小结果集作为驱动表

构造实验环境：

DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS PURGE;
CREATE TABLE t1 (
     id NUMBER NOT NULL,
     n NUMBER,
     contents VARCHAR2(4000)
   )
   ;
CREATE TABLE t2 (
     id NUMBER NOT NULL,
     t1_id NUMBER NOT NULL,
     n NUMBER,
     contents VARCHAR2(4000)
   )
   ;
execute dbms_random.seed(0);
INSERT INTO t1
     SELECT rownum, rownum, dbms_random.string('a', 50)
       FROM dual
     CONNECT BY level <= 100
      ORDER BY dbms_random.random;
INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000
    ORDER BY dbms_random.random;
COMMIT;

exec dbms_stats.gather_table_stats(ownname => user,tabname => 'T1',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;
exec dbms_stats.gather_table_stats(ownname => user,tabname => 'T2',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;
select count(*) from t1;
select count(*) from t2;

--在无索引的情况下查看执行计划，走hash算法。

实验1：

alter session set statistics_level=all;
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id;

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

Hash join优化要点

实验2：构造不准确的统计信息，使得Oracle 误认为T1是大表，T2是小表。

EXEC dbms_stats.SET_table_stats(user, 'T1', numrows => 20000000 ,numblks => 1000000);
EXEC dbms_stats.SET_table_stats(user, 'T2', numrows => 1 ,numblks => 1);

Hash join优化要点

alter session set statistics_level=all;
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Hash join优化要点

结论：理应A-Rows是不是100，从Used-Mem可以看到大表驱动小表的性能下降但是不明显。后期还需要验证。

要点3：确保PGA完成hash值运算尺寸

Hash算法原理：

Hash Join算法
第1步：判定小表是否能够全部存放在hash area内存中，如果可以，则做内存hash join。如果不行，转第二步。
第2步：决定fan-out数。(Number of Partitions) * C<= Favm *M 其中C为Cluster size，
其值为DB_BLOCK_SIZE*HASH_MULTIBLOCK_IO_COUNT；Favm为hash area内存可以使用的百分比，一般为0.8左右；M为Hash_area_size的大小。
第3步：读取部分小表S，采用内部hash函数(这里称为hash_fun_1)，将连接键值映射至某个分区，同时采用hash_fun_2函数对连接键值产生另外一个hash值，这个hash值用于创建hash table用，并且与连接键值存放在一起。
第4步：对build input建立位图向量。
第5步：如果内存中没有空间了，则将分区写至磁盘上。
第6步：读取小表S的剩余部分，重复第三步，直至小表S全部读完。
第7步：将分区按大小排序，选取几个分区建立hash table(这里选取分区的原则是使选取的数量最多)。
第8步：根据前面用hash_fun_2函数计算好的hash值，建立hash table。
第9步：读取表B，采用位图向量进行位图向量过滤。
第10步：对通过过滤的数据采用hash_fun_1函数将数据映射到相应的分区中去，并计算hash_fun_2的hash值。
第11步：如果所落的分区在内存中，则将前面通过hash_fun_2函数计算所得的hash值与内存中已存在的hash table做连接，将结果写到磁盘上。如果所落的分区不在内存中，则将相应的值与表S相应的分区放在一起。
第12步：继续读取表B，重复第9步，直至表B读取完毕。
第13步：读取相应的(Si,Bi)做hash连接。在这里会发生动态角色互换。
第14步：如果分区过后，最小的分区也比内存大，则发生nested- loop hash join。

要点1：两表的连接条件建立索引

要点2：小结果集作为驱动表

要点3：确保PGA完成hash值运算尺寸

相关推荐