9.SQL性能优化之子查询展开

1.什么是子查询展开 

SQL中出现子查询,不是把子查询当作一个独立的单元进行处理,而是将子查询转换为它自身
和外部查询之间等价的表连接。这种等价的表连接要么是将子查询拆开(将子查询中的表，视图
从子查询中拿出来，然后和外部查询中的表、视图做连接),要么是不拆开但是会把该子
查询转换为一个内嵌视图，然后再和外部查询中的表，视图做表连接。
只有当经过子查询展开后的等价改写的SQL的成本值小于原SQL的成本值时,Oracle才会对原SQL
执行子查询展开。子查询展开通常会提高原SQL的执行效率。因为如果原SQL不做子查询展开,
那么通常情况下该子查询就会在其执行计划的最后一步才被执行，并且会走FILTER类型的
执行计划，这也就意味着对于外部查询所在结果集中的每一条记录，该子查询都会被
当作一个独立单元来执行一次，外部查询所在的结果集有多少行，该子查询会被执行多少次。

--个人理解，后面这部分优点像连接谓词推入,视图外部有多少行，视图内部就要执行多少次。
适合做子查询展开的SQL: 
单行:=,<,>,<=,>=,<> 
EXISTS 
NOT EXISTS 
IN 
NOT IN 
ANY 
ALL 

IN=ANY 
NOT IN=(<>ALL)

2.子查看展开 

--实际执行一次。
set autotrace traceonly 
set timing on 
select t1.cust_last_name,t1.cust_id 
from customers t1 
where t1.cust_id in (select /*+no_unnest*/t2.cust_id from sales t2 where t2.amount_sold>700);

/*+no_unnest*/:表示不做子查询展开,此时子查询在最后一步执行,对应执行计划为FILTER;
取消HINT/*+no_unnest*/时该SQL不会做子查询展开。不做子查询展开,逻辑读一般也很高。
子查询展开，会将子查询里面的整体的结果集和外部的表做HASH JOIN或者HASH JOIN SEMI的连接,明显
提升性能,逻辑读和COST都会有较好的提升。

如果子查询多个表关联的结果集，则子查询会转换为一个内嵌视图VIEW,然后这个VIEW在和外部表
做子查询展开。
能否做子查询展开取决于两个条件:
(1)子查询展开所对应的等价改写SQL和原SQL在语义上一定要完全等价。
(2)对于不拆开子查询但是会把它转换为一个内嵌视图的子查询展开，只有当经过查询展开后的等价
改写SQL的成本值小于原SQL的成本值时，Oracle才会对目标SQL执行子查询展开。

子查询展开:并不总是能够提高性能，有适合不做子查询展开性能更好，需要针对具体的情况
进行分析。

3.总结 

(1)执行计划中出现 FILTER,可能就是子查询未做展开。
(2)适合做子查询展开的SQL特点:
	单行:=,<,>,<=,>=,<> 
	EXISTS,NOT EXISTS,IN,NOT IN, ANY,ALL 
(3)核心原则:如果外部表查询结果集较大,走NEST LOOP/FILTER的执行计划的效率很低,
	将NEST LOOP,转换为HASH JOIN/HASH JOIN SEMI,提升性能。
(4)遇到有子查询的SQL,有可能做子查询展开SQL性能高，有可能不做子查询展开SQL性能高,并不绝对。