表连接方式(4)-标量子查询、半连接、反链接优化方案总结

一.标量子查询

数据库经常能看到如下类形式的SQL:

select t.OWNER,t.OBJECT_NAME,(select TABLESPACE_NAME from t1 where 
t.object_name=t1.segment_name) travelskydba from t;

我们看到,该SQL中的查询列是由一个单独的SQL查询组成,查询出T表与T1表关联Join后的T1表中的数据。可以看到,T表与T1表是存在关联关系的,t.object_name=t1.segment_name,此类SQL我们成为标量子查询。

sub-1我们从执行计划中看到,T1表扫描了5012次,T表扫描了1次,是否有点类似于嵌套循环(Nested Loop)的感觉?标量子查询类似于嵌套循环,本例子中,T表为主表,T1为子表,主表通过t.object_name=t1.segment_name传值给子表进行Filter过滤,不同于嵌套循的是,标量子查询的驱动表固定为主表,并不可更改,需要T表传值与T1表。

(1)标量子查询在SQL优化的过程中需要注意,子表(被驱动表)扫描次数的问题,也就是执行计划中的starts列,主表放回多少数据,就要传值与子表,如果数据量很大,子表扫描次数会非常庞大,造成SQL的性能问题。同嵌套循环一致,被驱动表连接列上需要创建选择性好的索引。

(2)在Oracle 12c中,标量子查询会等价改写为外连接,从而可以改变执行计划进行HASH_JOIN

SUB-2

 

为什么是外连接而不是内连接呢?我们往EMP表中插入一行数据:

20191102-1

如果存在主表EMP.DEPTNO的情况,改写为内连接会丢失ALBERT此行数据,所以标量子查询在不确定表业务特性时,一定要改写为外连接。

二.半连接:

当SQL出现in ,exists时,我们称为半连接,例如:

select * from emp where DEPTNO in (select DEPTNO from dept)
select * from emp where exists (select 1 from dept where emp.DEPTNO =dept.DEPTNO);

in-1

exist-1

我们看到,EMP为驱动表,DEPT为被驱动表,我们可以改变内连接的执行计划:

qbname-11

此执行计划为EMP为驱动表,DEPT为被启动表进行嵌套循环。

qb_name-2

我们通过添加HINT控制执行计划后,将DEPT表作为驱动该表,此方法可以帮助我们在办连接中,控制其执行计划。

半连接通常可以等价改写成内连接的方式,例如:

select *  from dept where deptno in (select deptno from emp)可以等价改写成

select dept.* from dept,(select deptno from emp group by deptno)  where dept.deptno=emp.depto(因为返回的是DEPT表的数据,所以要将EMP进行去重操作,避免重复数据,改变结果集)此改写方式因多了去重的操作,所以执行效率并不如返连接高。

三.反连接:

AB表进行关联,只返回A表其中没有成功关联上B表的数据,我们称为反连接,not in ,not exists关键字,我们看如下例子:

1011-1

我们观察结果是NULL值,1400和0并没有如愿得出,是因为not in对空值敏感,如果该列包含空值,结果集会全部显示为空,因为NULL不等于任何值。剔除空值,则便显示正确结果:

1011-2

exists对空值不敏感,空值皆可返回。

1011-3

反连接执行计划可以改写成外连接+子表连接列 is null的情况,也同样可以利用qb_name hint改变执行计划,但是改写成外连接后,启动表被固定为主表,无法更换驱动表(主表存在NULL无法传值的情况)。

1011-4