本次主要讲解执行计划中表连接的几个基础概念
本次主要讲解执行计划中表连接的几个基础概念
- 1、数据集处理
- 2、where后的条件类型(限制条件和连接条件、access和filter)
- 3、连接的类型
1、数据集处理
数据库引擎支持的所有的连接方法都是每次只能处理两个结果集。如下执行计划所示，
explain plan for
select /*+ ordered use_nl(c,a,d) full(c) full(a) full(d) */count(1) from ht.c_cons c,ht.a_amt a,ht.c_cons_ds d where c.cons_no=a.cons_no and a.cons_no=d.cons_no;
select * from table(dbms_xplan.display);
Plan hash value: 327334001
----------------------------------------------------------------------------------
| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 15 | 1675K (1)| 05:35:01 |
| 1 | SORT AGGREGATE |
| 1 | 15 |
|
|
| 2 | NESTED LOOPS
|
| 60586 | 887K| 1675K (1)| 05:35:01 |
| 3 | NESTED LOOPS |
| 60586 | 591K| 670K (1)| 02:14:12 |
| 4 | TABLE ACCESS FULL| C_CONS
| 10103 | 50515 | 17 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| A_AMT
| 6 | 30 | 66 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | C_CONS_DS | 1 | 5 | 17 (0)| 00:00:01 |
----------------------------------------------------------------------------------
2、where后的条件类型
Oracle数据库where后的条件类型分为：连接条件和限制条件2种类型。
存在谓词的操作步骤ID左边有个*号
。
- 谓词类型分为：access和filter 2种。
条件类型：
连接条件： c.cons_no=a.cons_no
限制条件：and a.amt_ym='201701' and c.cons_name='wang1706'
- 谓词类型：
ACCESS:
5 - access("C"."CONS_NAME"='wang1706')
6 - access("C"."CONS_NO"="A"."CONS_NO")
FILTER:
7 - filter("A"."AMT_YM"='201701')
- ACCESS 和filter区别
FILTER: 访问整个数据集，过滤不符合条件的行，只返回符合条件的行。该操作可能会做无用功。
ACCESS: 按照条件只访问符合条件的行。官方解释如下所示，
***************filter示例******************************
set autot trace
select count(1) from ht.c_cons c where org_name='guangdong';
Execution Plan
----------------------------------------------------------
Plan hash value: 605022138
-----------------------------------------------------------------------------
| Id | Operation
| Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
|
1 |
9 |
17 (0)| 00:00:01 |
| 1 | SORT AGGREGATE |
|
1 |
9 |
|
|
|* 2 | TABLE ACCESS FULL| C_CONS |
842 | 7578 |
17 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ORG_NAME"='guangdong')
***************access示例****************************
select count(1) from ht.c_cons c where cons_no=101600;
Execution Plan
----------------------------------------------------------
Plan hash value: 1250298410
-----------------------------------------------------------------------------------
| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
|
1 |
5 |
1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE |
|
1 |
5 |
|
|
|* 2 | INDEX UNIQUE SCAN| SYS_C0011125 |
1 |
5 |
1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CONS_NO"=101600)
3、连接的类型
- 1 交叉连接
交叉连接(cross join),也叫笛卡尔乘积，是一种将一张表的所有记录与另一张表的所有记录进行组合的操作。
例如：
select count(1) from ht.c_cons c,ht.a_amt a where c.cons_no=a.cons_no;
select count(1) from ht.c_cons c,ht.a_amt a;
或
select count(1) from ht.c_cons c cross join ht.a_amt a;
- 2 条件连接
条件连接，交叉连接的一个子集，仅返回符合条件的结果。
select count(1) from ht.c_cons c,ht.a_amt a where c.cons_no between 600001 and 610001;
或
select count(1) from ht.c_cons c inner join ht.a_amt a on c.cons_no between 600001 and 610001;
或(关键字inner可被省略)
select count(1) from ht.c_cons c join ht.a_amt a on c.cons_no between 600001 and 610001;
- 3 等值连接
等值连接也称为自然连接(natural join)，连接条件里只有等于操作符。
select count(1) from ht.c_cons c,ht.a_amt a where c.cons_no=a.cons_no;
或
select count(1) from ht.c_cons c join ht.a_amt a on c.cons_no=a.cons_no;
- 4 自然连接
表跟自己连接
select emp.ename,mgr.ename from emp,emp mgr where emp.mgr=mgr.empno;
或
select emp.ename,mgr.ename from emp join emp mgr on emp.mgr = mgr.empno;
- 5 外连接
外连接是对条件连接的扩展，会返回一个表中的所有记录，对于没有匹配的相关记录的字段返回NULL。
左连接
select count(1) from ht.c_cons c,ht.a_amt a where c.cons_no=a.cons_no(+);
或
select count(1) from ht.c_cons c left join ht.a_amt a on c.cons_no=a.cons_no;
或
select count(1) from ht.c_cons c left outer join ht.a_amt a on c.cons_no=a.cons_no;
右连接
select count(1) from ht.c_cons c,ht.a_amt a where c.cons_no(+)=a.cons_no;
或
select count(1) from ht.c_cons c right join ht.a_amt a on c.cons_no=a.cons_no;
或
select count(1) from ht.c_cons c right outer join ht.a_amt a on c.cons_no=a.cons_no;
全连接
select count(1) from ht.c_cons c full join ht.a_amt a on c.cons_no=a.cons_no;
或
select count(1) from ht.c_cons c full outer join ht.a_amt a on c.cons_no=a.cons_no;
- 6 半连接
当一张表在另一张表找到匹配的记录之后，半连接只返回其中一张表的记录。
;
select cons_no,amt from ht.a_amt where cons_no in (
select cons_no from ht.c_cons where cons_name in (
'zhang360',
'zhang361',
'zhang362',
'zhang363'
)
) and AMT_YM='201701';
或
select cons_no,amt from ht.a_amt a where
exists( select 1 from ht.c_cons c where c.cons_no=a.cons_no
and c.cons_name in (
'zhang360',
'zhang361',
'zhang362',
'zhang363'
)
) and amt_ym='201701';
- 7 反连接
当一张表在另一张表找不到匹配的记录之后，半连接只返回其中一张表的记录。
select cons_no,amt from ht.a_amt where cons_no not in (
select cons_no from ht.c_cons where cons_name in (
'zhang360',
'zhang361',
'zhang362',
'zhang363'
)
) and AMT_YM='201701';
或
select cons_no,amt from ht.a_amt a where
not exists ( select 1 from ht.c_cons c where c.cons_no=a.cons_no
and c.cons_name in (
'zhang360',
'zhang361',
'zhang362',
'zhang363'
)
) and amt_ym='201701';