三种连接方式的适用范围和效率比较

适用范围

　　Nested loop join:

　　适用于outer table(有的地方叫Master table)的记录集比较少(<10000)而且inner table(有的地方叫Detail table)索引选择性较好的情况下(inner table要有index)。

　　inner table被outer table驱动，outer table返回的每一行都要在inner table中检索到与之匹配的行。当然也可以用ORDERED 提示来改变CBO默认的驱动表，使用USE_NL(table_name1 table_name2)可是强制CBO 执行嵌套循环连接。

　　cost = outer access cost + (inner access cost * outer cardinality)

　　Sort merge join：

　　用在数据没有索引但是已经排序的情况下。

　　通常情况下hash join的效果都比Sort merge join要好，然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时Sort merge join的性能会优于hash join。可以使用USE_MERGE(table_name1 table_name2)来强制使用Sort merge join。

cost = (outer access cost * # of hash partitions) + inner access cost

　　Hash join：

　　适用于两个表的数据量差别很大。但需要注意的是：如果HASH表太大，无法一次构造在内存中，则分成若干个partition，写入磁盘的temporary segment，则会多一个I/O的代价，会降低效率，此时需要有较大的temporary segment从而尽量提高I/O的性能。

　　可以用USE_HASH(table_name1 table_name2)提示来强制使用散列连接。如果使用散列连HASH_AREA_SIZE 初始化参数必须足够的大，如果是9i，Oracle建议使用SQL工作区自动管理，设置WORKAREA_SIZE_POLICY 为AUTO，然后调整PGA_AGGREGATE_TARGET 即可。

　　也可以使用HASH_JOIN_ENABLED=FALSE(默认为TRUE)强制不使用hash join。

　　cost = (outer access cost * # of hash partitions) + inner access cost

　　效率比较

　　Hash join的主要资源消耗在于CPU(在内存中创建临时的hash表，并进行hash计算)，而merge join的资源消耗主要在于磁盘I/O(扫描表或索引)。在并行系统中，hash join对CPU的消耗更加明显。所以在CPU紧张时，最好限制使用hash join。

　　在绝大多数情况下，hash join效率比其他join方式效率更高：

　　在Sort-Merge Join(SMJ)，两张表的数据都需要先做排序，然后做merge。因此效率相对最差;

　　Nested-Loop Join(NL)效率比SMJ更高。特别是当驱动表的数据量很大(集的势高)时。这样可以并行扫描内表。

　　Hash join效率最高，因为只要对两张表扫描一次

连接铁律

1.若有单行谓词，则他的表一定是驱动表（select *  from employees e,departments d where e.department_id=d.department_id and e.department_id=100 and salary=10000;    上面的语句中e.department_id=d.department_id是连接谓词，e.department_id=100是非连接谓词（对连接列的限制），salary=10000是单行谓词（对非连接列的限制））

2.外连接时，一定是用显示的行数比较多的那个表作为驱动表。如：

select e.employee_id,e.department_id,d.manager_id,d.location_id from employees e right join departments d on e.department_id=d.department_id

则departments表显示的行数一定大于等于employees表，所以应该要以departments表作为驱动表，如果以employees表作为驱动表，则departments表中多显示的那几行就显示不出来了

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24984814/viewspace-708159/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24984814/viewspace-708159/