20 查询变换(subquery unnesting) --优化主题系列
前面提到过半连接/反连接，当时我说，大家不要去记什么时候用in,exists,notin ,not exists。后面会让大家理解in,exists/notin,not exists最底层的原理。那么现在我们就来探索一下他们的最底层原理。
子查询非嵌套(subqueryunnesting)
在学习subqueryunnesting之前，先深入理解执行计划中的FILTER，可以这样说，绝大部分复杂的子查询，性能问题基本上都是出现在FILTER上面。现在举个例子解释一下什么是FILTER。(你们要模拟就自己创建一个test表，数据从dba_objects复制)
CREATE TABLE TEST1 AS SELECT * FROM DBA_OBJECTS;
CREATE TABLE TEST2 AS SELECT * FROM DBA_OBJECTS;
set autot trace
select * from test1 where owner='SCOTT' or object_id in(selectobject_id from test2 where owner='SCOTT');
注意：我指的FILTER是Operation里面的FILTER，不是指的谓词过滤里面的FILTER。
这个FILTER是不是有两个儿子？？下面谓词的FILTER 是 where条件的过滤条件
查看高级执行计划：
ALTER SESSION SET STATISTICS_LEVEL=ALL;
select * from test1 where owner='SCOTT' or object_id in(selectobject_id from test2 where owner='SCOTT');
SELECT * FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
看 ID=3扫描了多少次？？是不是类似NL？？驱动表返回多少行被驱动要扫描多少次
NL是百分百精确的FILTER不是百分百精确的72832–72816=16
上面的表跑不出结构的原因为 TEST1表返回7W多行数据 TEST2是不是要被扫描7W多次？？
假设TEST2 有5MB数据 7W*5MB=350GB 要扫描350GB的数据是不是要很久？？
为什么建索引会很快出结果？？5MB数据建立索引是不是不到1MB？？350GB-->变到不到1GB
如果并发大了同时有20个进程都在执行这个SQL 是不是这些块要被扫描上百万次？？
如果这个SQL 我能让它扫描次数从7W次变成1次是不是最优化的？？
是不是要改写SQL？？用UNION看下执行计划
select * from test1 union select * from test1 where object_idin(select object_id from test2 where owner='SCOTT');
是不是改写后的SQL 瞬间出结果？？
还有另外一种FILTER，单操作的FILTER，执行计划如下：
只有一个儿子是单操作
filter下两个或多个儿子、谓词过滤里有EXSITS和:绑定变量B1 这种一定要注意
可能被扫描几百次
DW_BO_ORDER这个表是不是哟200W数据？？扫描它上百次是不是可能扫描几十亿条数据？？
理解了FILTER，我们再来看subqueryunnesting
Subquery Unnesting(子查询非嵌套)：如果SQL语句中的where条件后面有子查询，子查询前面有in,notin,exists,not exists,<,<=,=,>,>= 等等，CBO很可能会对该子查询进行等价改写，改写的过程其实就叫做子查询扩展。Oracle始终认为SQL语句进行改写之后，CBO能更好的优化，当然了，并不是所有的子查询都会被改写，子查询中有些限制条件会阻止CBO进行改写(因为改写之后不等价)。
如何才能查看SQL进行了subqueryunnesting?
请自己翻阅之前讲解执行计划的章节，这里再提一下，利用下面SQL可以查看到
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ADVANCED-PROJECTION'));
Disable Subquery Unnesting: alter session set"_unnest_subquery"=false;
subquery unnesting 例子(基于scott Oracle11gR2)
select ename,deptnofrom emp where deptno in (select deptno from dept where dname='CHICAGO');
其实该SQL语句等价于如下SQL，并且CBO也会将它改写为如下SQL
select e.ename,e.deptno from emp e,dept d wheree.deptno=d.deptno and d.dname='CHICAGO';
这2个SQL语句的执行计划是一模一样的，当我们执行第一个SQL语句的时候，CBO就会将其改写为第二个SQL，这个过程就叫做SubqueryUnnesting
用hintNO_UNNEST 可以禁止CBO进行 SubqueryUnnesting
hint UNNEST 可以提示CBO进行Subquery Unnesting
上面HINT只能放在子查询里面
select ename,deptno from emp where deptno in (select /*+NO_UNNEST*/deptno from dept where dname='CHICAGO');
如果没有发生subqueryunnesting，执行计划中就有FILTER。当你拿到一个执行计划，如果发现有FILTER，就要注意仔细查看了。
没有进行 SUBQUERYUNNESTING 执行计划有哪些关键字？？
如果有 FILTER 后面跟括号 EXISTS或者 FILTER 括号 NOTEXISTS后面绝对有绑定变量
有时候有FILTER跑的慢但有时候有FILTER 又跑的快
如果在执行计划中见到FILTER关键字首先要看有几个儿子
一个儿子不理它如果两个或多个儿子那么
第一个儿子要看返回行数，即驱动表返回多少行。
是不是要去看DISTINCT 值？子查询前面的列
另外要去看被驱动表是不是走全表扫描了？
第二个儿子看体积要去看被驱动表的SEGMENT_SIZE
假设第二个儿子有10GB第一个儿子返回100行是不是要扫描1TB？？
执行计划发现有 FILTER 那尽量让他们不走FILTER