Decode360's Blog

业精于勤而荒于嬉 QQ:150355677 MSN:decode360@hotmail.com

  BlogJava :: 首页 :: 新随笔 :: 联系 ::  :: 管理 ::
  397 随笔 :: 33 文章 :: 29 评论 :: 0 Trackbacks

Oracle中表的连接类型

 

    Oracle中所谓表的连接,有两种含义。一种是根据Oracle内部的连接判断方式区分的内部连接类型,这会影响到系统优化、资源占用等问题。另一种是基于查询的方式,对两张表之间建立联系,会影响到查询的结果,这里都写一下。

 

 

一、Oracle 中的表内部连接有 3类

 

1、 嵌套循环连接( Nested Loops )

 

    原理:扫描一个表,每读取驱动表的一条记录,就根据索引去另一个表里面查找,所有匹配记录放在结果集中,然后再读取驱动表的下一行。没有索引一般就不会是 nested loops

    条件:驱动表结果集不大,被驱动表连接字段要有索引。

    特点:使用嵌套循环连接是从连接结果中提取第一批记录的最快速方法。

    使用: USE_NL(t1 t2) 提示来强制执行 Nested Loops

 

2、 哈希连接( Hash Join )

 

    原理:优化器先扫描小表,根据连接键在内存中建立 hash 表,然后扫描大表,每得到一条记录就探测 hash 表一次,找出匹配行。

    条件:两个巨大表之间的连接,或一个巨大的表一个小表之间的连接。且连接键无索引。

    特点:需要较大的内存,如表太大则需要进行分区,并暂时存储至磁盘的临时段。扫描成本 = 全表扫描大表 + 分区数 * 表全表扫描小表;还需要注意的是:必须将 HASH_JOIN_ENABLED 设为 True, 并且为参数 PGA_AGGREGATE_TARGET 设置了一个足够大的值后,才可以执行 Hash Join

    使用: USE_HASH(t1 t2) 提示来强制执行 Hash Join

 

3、 排序合并连接( Sort Merge Join )

 

    原理:将两个表分别进行排序,然后将两个表合并,查找出匹配的记录。

    条件:行源已经被排过序的情况下使用。

    特点:主要花费在两个表的全表扫描和各自的排序上。

    使用: USE_MERGE(t1 t2) 提示来强制执行 Sort Merge Join

 

 

    总结 :当缺少有用的索引时,哈希连接比嵌套循环连接更加有效。哈希连接可能比排序合并连接更快,因为在这种情况下只有一张源表需要排序。哈希连接也可能比嵌套循环连接更快,因为处理内存中的哈希表比检索 B_Tree 索引更加迅速。

 

 

 

二、Oracle外部连接方式也分3类:

 

    SQL> select * from t11;

            C1         C2
    ---------- ----------
             5          5
             1          1
             2          2

    SQL> select * from t22;

            D1         D2
    ---------- ----------
             1          1
             2          2
             3          3
             4          4

 

1、内连接(inner join)

 

    SQL> select c1,c2,d1,d2 from t11 inner join t22 on t11.c1=t22.d1;

 

    等效于:(可简写为join)

    SQL> select c1,c2,d1,d2 from t11,t22 where t11.c1=t22.d1;

 

    效果:

            C1         C2         D1         D2
    ---------- ---------- ---------- ----------
             1          1          1          1
             2          2          2          2

 

2、外连接(outer join)

 

  左外连接

 

    SQL> select c1,c2,d1,d2 from t11 left outer join t22 on t11.c1=t22.d1;

 

    等效于:(可简写为left join)

    SQL> select c1,c2,d1,d2 from t11,t22 where t11.c1=t22.d1(+);

 

    效果:

            C1         C2         D1         D2
    ---------- ---------- ---------- ----------
             5          5
             1          1          1          1
             2          2          2          2

 

  右外连接

 

    SQL> select c1,c2,d1,d2 from t11 right outer join t22 on t11.c1=t22.d1;

 

    等效于:(可简写为right join)

    SQL> select c1,c2,d1,d2 from t11,t22 where t11.c1(+)=t22.d1;

 

    效果:

            C1         C2         D1         D2
    ---------- ---------- ---------- ----------
             1          1          1          1
             2          2          2          2
                                   4          4
                                   3          3

  全外连接

 

    SQL> select c1,c2,d1,d2 from t11 full outer join t22 on t11.c1=t22.d1;

 

    效果:

            C1         C2         D1         D2
    ---------- ---------- ---------- ----------
             5          5
             1          1          1          1
             2          2          2          2
                                   4          4
                                   3          3

 

    等效于:(可简写为full join)

    SQL> select c1,c2,d1,d2 from t11,t22 where t11.c1=t22.d1(+)
      2  union
      3  select c1,c2,d1,d2 from t11,t22 where t11.c1(+)=t22.d1;

 

3、交叉连接(cross join)

 

    SQL> select c1,c2,d1,d2 from t11 cross join t22;

 

    等效于:(其他join必须有on子句)

    SQL> select c1,c2,d1,d2 from t11,t22;

 

    效果:

            C1         C2         D1         D2
    ---------- ---------- ---------- ----------
             5          5          1          1
             5          5          2          2
             5          5          3          3
             5          5          4          4
             1          1          1          1
             1          1          2          2
             1          1          3          3
             1          1          4          4
             2          2          1          1
             2          2          2          2
             2          2          3          3

             2          2          4          4

    12 rows selected.

 

 

 

posted on 2008-08-12 22:10 decode360 阅读(888) 评论(0)  编辑  收藏 所属分类: 07.Oracle

只有注册用户登录后才能发表评论。


网站导航: