Oracle之三大表连接优化大全

1.nl连接，请用在局部扫描额场景

nl第一步优化：驱动表限制条件有索引

第二步：被驱动表的连接条件有索引

第三步：确保小结果集先驱动

2.hash连接：

第一步：两表限制条件有索引

第二步：小结果集驱动

第三步：尽量保证PGA能容纳hash算法

3.merge sort join连接：

第一步：两表限制条件有索引

第二步：连接条件索引消除排序（排序本身有序）

只能消除一边的排序，根本不可能消除两边的排序。（oracle承认了。。）

第三步：避免去多余列致排序尺寸过大

第四步：保证PGA的尺寸要大

（先放着。。。以后看好了）

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。