Oracle SQL调优系列之表连接学习笔记
Oracle SQL调优系列之表连接学习笔记
文章目录
一、表连接类型
表连接类型可以分为：内连接、外连接，在看《收获，不止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.idinner关键字可以省略
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连接没有限制，这是几种表连接方法的区别