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