Oracle SQL调优系列之表连接学习笔记

你的名字 2021-09-20 10:06 395阅读 0赞

### 文章目录 ###

*   *  一、表连接类型
     *   *  1.1 内连接
         *  1.2 外连接
     *  二、表连接方法
     *   *  2.1 表连接方法分类
         *  2.2 表连接方法特性区别

## 一、表连接类型 ##

表连接类型可以分为：内连接、外连接，在看《收获，不止sql优化》一书并做了笔记

### 1.1 内连接 ###

内连接：指表连接的结果只包含那些完全满足连接条件的记录。下面学习一下内连接的，给个例子，这里创建两张表，然后用内连接方式查询，看看例子：

SQL>select * from t1;
    id   col1
    ---- ----
      A    A1
      B    B1
      C    C1
      D    D1
    SQL>select * from t2;
    id    col2
    ---- ----
      A    A2
      C    B2
      D    C2
      E    D2
    SQL>select * from t1,t2 where t1.id=t2.id;
    id   col1 col2
    ---- ---- ----
     A    A1    A2
     C    C1    C2
     D    D1    D2

> 判断标准：SQL中没有定义外连接的left outer join、right outer join、full outer join以及(+)符合，这种SQL统一叫内连接，所以sql中不能有一个外连接的标识SQL，不然整条sql都变成了外连接

Oracle(+)符号用法：

> Oracle左右连接可以使用(+)，+号在左表示右外连接，在右表示左外连接

例子，下面的sql都属于内连接：

t1,t2方法

select * from t1,t2 where t1.id = t2.id;

inner join on方法

select * from t1 inner join t2 on t1.id = t2.id

inner关键字可以省略

select * from t1 join t2 on (t1.id=t2.id);

join using方法

select * from t1 join t2 using(id);

### 1.2 外连接 ###

外连接：外连接是对内连接的拓展，它是指包含完全符合的记录之外，还会包含驱动表所有不符合的连接条件的记录

左连接的情况

SQL>select * from t1;
    id   col1
    ---- ----
      A    A1
      B    B1
      C    C1
      D    D1
    SQL>select * from t2;
    id    col2
    ---- ----
      A    A2
      C    B2
      D    C2
      E    D2
    SQL>select * from t1 left join t2 on t1.id=t2.id;
    id   col1 col2
    ---- ---- ----
     A    A1    A2
     B    B1    
     C    C1    C2
     D    D1    D2

右连接，反过来，以被驱动表t2为准；全外连接就是不管驱动表t1还是被驱动表t2全都查出来，不管是否符合连接条件，语法是full join on

## 二、表连接方法 ##

### 2.1 表连接方法分类 ###

两个表之间的表连接方法有排序合并连接、嵌套循环连接、哈希连接、笛卡尔连接

*  排序合并连接（merge sort join）
 *  嵌套循环连接（Nested loop join）
 *  哈希连接（Hash join）
 *  笛卡尔连接（Cross join）

### 2.2 表连接方法特性区别 ###

**（1）表访问次数区别**

使用Hint语法强制使用nl

select /*+ leading(t1) use_nl(t2)*/ * from t1,t2
    where t1.id = t2.id
    and t1.id in (17,19);

查看执行计划

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
    
    PLAN_TABLE_OUTPUT
    
    SQL_ID  245z7n1cxaf3m, child number 0
    -------------------------------------
    SELECT /*+ leading(t1) use_nl(t2)*/ * FROM t1, t2 WHERE t1.id = t2.t1_id
    
    Plan hash value: 1967407726
    
    --------------------------------------------------------------------------------
    -----
    | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buff
    ers |
    --------------------------------------------------------------------------------
    -----
    |   0 | SELECT STATEMENT   |      |      1 |        |    300 |00:00:00.25 |   29
    747 |
    |   1 |  NESTED LOOPS      |      |      1 |    300 |    300 |00:00:00.25 |   29
    747 |
    |   2 |   TABLE ACCESS FULL| T1   |      1 |    300 |    300 |00:00:00.01 |
     27 |
    |*  3 |   TABLE ACCESS FULL| T2   |    300 |      1 |    300 |00:00:00.25 |   29
    720 |
    --------------------------------------------------------------------------------
    -----
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - filter("T1"."ID"="T2"."T1_ID")
    
    Note
    
    PLAN_TABLE_OUTPUT
    
       - dynamic sampling used for this statement (level=2)
    
    
    已选择24行。

Nested sort join中，驱动表被访问0或1次，被驱动表被访问0或者n次,n是驱动表返回的结果集条数

然后同样可以进行hash join、merge join的实践，hash join用/\*+ leading(t1) use\_hash(t2) \*/

Hash join中驱动表被访问0或者1次，被驱动表也一样

merge sort join中驱动表被访问0或者1次，被驱动表也一样

**（2）表连接顺序影响**

对于前面的用t1为驱动表的情况，现在换一下顺序，

SQL>SELECT /*+ leading(t2) use_nl(t1)*/ * FROM t1, t2 WHERE t1.id = t2.t1_id;
    SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
    
    PLAN_TABLE_OUTPUT
    
    SQL_ID  fgw5v7y16yn4m, child number 0
    -------------------------------------
    SELECT /*+ leading(t2) use_nl(t1)*/ * FROM t1, t2 WHERE t1.id = t2.t1_id
    
    Plan hash value: 4016936828
    
    --------------------------------------------------------------------------------
    -----
    | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buff
    ers |
    --------------------------------------------------------------------------------
    -----
    |   0 | SELECT STATEMENT   |      |      1 |        |    300 |00:00:00.30 |   70
    139 |
    |   1 |  NESTED LOOPS      |      |      1 |    300 |    300 |00:00:00.30 |   70
    139 |
    |   2 |   TABLE ACCESS FULL| T2   |      1 |   9485 |  10000 |00:00:00.01 |
    119 |
    |*  3 |   TABLE ACCESS FULL| T1   |  10000 |      1 |    300 |00:00:00.29 |   70
    020 |
    --------------------------------------------------------------------------------
    -----
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - filter("T1"."ID"="T2"."T1_ID")
    
    Note
    
    PLAN_TABLE_OUTPUT
    
       - dynamic sampling used for this statement (level=2)
    
    
    已选择24行。

> 可以看出表连接顺序对NL连接是有影响的，同理实验，可以看出对hash join也是有影响的，而merger join不影响

**（3）表连接排序**

对于这几种表连接，可以用set autotrace on方式查看sorts属性，可以得出只有merge join是有排序的，Nl连接和hash join是无序的

**（4）各表连接失效情况**

> hash join不支持的条件是“>、<、<>、like”的连接方式，merge join不支持的条件是“<>、like”支持“<、>”的情况，而nl连接没有限制，这是几种表连接方法的区别