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

举报
yd_273762914 发表于 2020/12/02 23:58:48 2020/12/02
【摘要】 文章目录 一、表连接类型1.1 内连接1.2 外连接 二、表连接方法2.1 表连接方法分类2.2 表连接方法特性区别 一、表连接类型 表连接类型可以分为:内连接、外连接,在看《收获,不止sql优化》一书并做了笔记 1.1 内连接 内连接:指表连接的结果只包含那些完全满足连接条件的记录。下面学习一下内连接的,给个例子,这里创建两张表,然后用内...

一、表连接类型

表连接类型可以分为:内连接、外连接,在看《收获,不止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连接没有限制,这是几种表连接方法的区别

文章来源: smilenicky.blog.csdn.net,作者:smileNicky,版权归原作者所有,如需转载,请联系作者。

原文链接:smilenicky.blog.csdn.net/article/details/93461506

【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。