ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 三种连接方式的适用范围和效率比较

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

原创 Linux操作系统 作者:wengjie_0627 时间:2011-09-23 09:22:07 0 删除 编辑
适用范围

  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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: hash join详解
下一篇: oracle中组函数
请登录后发表评论 登录
全部评论

注册时间:2011-05-19

  • 博文量
    50
  • 访问量
    27731