12.查询转换-视图合并技术

1.什么是视图合并 

视图合并 是优化器处理带视图的目标SQL的一种优化手段，它是指不再将目标SQL
中视图的定义SQL语句当作一个独立的处理单元单独执行，而是将其拆开，
把其定义SQL语句中的基表拿出来于外部查询的表合并，这样合并后的SQL将只剩下
外部查询中的表和原视图中的基表，不再会有视图出现。

视图合并的好处和子查询展开一样，都是让优化器有更多的执行路径可以选择，
而不再拘泥于原视图定义SQL语句中的谓词条件，表连接方法，表连接顺序等。

视图合并分为:简单视图合并,复杂视图合并，外连接视图合并。
对于符合简单视图合并条件的目标SQL，Oracle始终会对其做视图合并，而不管
经过视图合并后的等价改写SQL的成本值是否小于原SQL的成本值。在oracle10g
及其以后的版本中，对于复杂视图合并，只有等价改写SQL的成本值小于原来
SQL的成本值时，Oracle才会对目标SQL做复杂视图合并。

2.简单视图合并的例子

select t1.prod_id,t1.prod_name 
from products t1,view_1 
where t1.prod_id=view_1.prod_id
and t1.prod_list_price=>1000;

create or replace view view_1 as 
select t2.prod_id as prod_id
from sales t2 ,customers t3 
where t2.cust_id=t3.cust_id
and t2.cust_gender='MALE';


做视图合并之后，优化器有了更多的执行路径可以选择，而不再拘泥于原视图定义SQL 
语句中的谓词条件，表连接方法，表连接顺序等。

做了视图合并，表的执行顺序:CUSTOMERS->(PRODUCTS->SALES);
PRODUCTS和SALES,做HASH JOIN,CUSTOMERS再和其结果做连接。

如果不做简单视图合并:
使用HINT不让SQL做简单视图合并。
select /*+no_merger(view_1)*/t1.prod_id,t1.prod_name 
from products t1,view_1 
where t1.prod_id=view_1.prod_id
and t1.prod_list_price=>1000;

不做视图合并，视图部分单独执行，即CUSTOMERS和SALES会直接连接，形成视图，
然后PRODUCTS再和视图连接。CUSTOMERS和SALES 通过HASH JOIN 形成VIEW执行计划。
VIEW  --->VIEW_1 

一般来说，如果Oracle并没有选择对带视图的目标SQL执行视图合并的话，那么在
该SQL的执行计划中就会见到VIEW 关键字，并且该关机字所对应的NAME列就是视图的名称。

做了简单视图合并后的等价改写SQL只是让优化器有了更多的执行路径可以选择，原来
的未做简单视图合并情况下的执行路径还是保留了的，所以做了简单视图合并后的
等价改写SQL的成本值一定是小于或等于未做简单视图合并的原SQL的成本值。
视图定义的SQL语句中有UNION ALL时不能做简单视图合并。

视图合并相关的HINT;
/*no_merge(VIEW_1) cadinality(t1 100)*/ :不做视图合并 
/*+merge(VIEW_1) cadinality(t1 100)*/   :做视图合并
HINT: cadinality 定义表的返回行数,能够引起执行计划的改变。调小T1表访问成本。

3.外连接视图合并 

外连接视图合并是指针对那些使用了外连接,以及所带视图的视图定义SQL语句中不包含
DISTINCT,GROUP BY等聚合函数的目标SQL的视图合并。这里的"外连接"指外部查询的表
和视图之间使用了外连接,或者该视图的视图定义SQL语句中使用了外连接。

外连接会给视图合并带来很多限制，很多在内连接情况下可以做的视图合并一旦换成了
外连接就不能做了，因为Oracle能做视图合并的前提条件是视图合并的等价改写SQL 
一定要和原SQL在语义上是完全等价的。

外连接视图合并有一个很常用的限制，即当目标视图在和外部查询的表做外连接时，
该目标视图可以做外连接视图合并的前提条件是，要么该视图被作为外连接的驱动表，
要么该视图虽然被作为外连接的被驱动表，但它的视图定义SQL语句中只包含一个表。

--外连接视图的SQL;
select t1.prod_id,t1.prod_name
from products t1 view_1_modify
where t1.prod_id(+)=view_1_modify.prod_id;

create view view_1_modify as 
select t2.prod_id as prod_id
from sales t2,customers t3 
where t2.cust_id=t2.cust_id;

HASH JOIN RIGHT OUTER 执行计划,未出现VIEW,说明做了外连接视图合并。
视图作为驱动表。

当视图要做被驱动表时，视图中只能有一个表，这样才能做外连接视图合并。

4.复杂视图合并 

复杂视图合并是指针对视图定义中SQL语句中含有GROUP BY ,DISTINCT的目标SQL的视图合并。

create view view_3 as 
select cust_id,prod_id,sum(quatity_sold) total 
from sales 
group by cust_id,prod_id;

select t1.cust_id,t1.cust_last_name 
from customers t1,products t2 ,view_3 t3 
where t1.cust_id=t3.cust_id 
and t2.prod_id=t3.prod_id
and t3.total>700 
and t2.prod_category='Hardware';

HASH GROUP BY 执行计划被推到外部。VIEW_3的视图定义SQL语句中的group by 被oracle
延迟做完三个表(sales,customers,products)的表连接后才执行。
--不做视图合并
select /*+no_merge(t3)*/t1.cust_id,t1.cust_last_name 
from customers t1,products t2 ,view_3 t3 
where t1.cust_id=t3.cust_id 
and t2.prod_id=t3.prod_id
and t3.total>700 
and t2.prod_category='Hardware';

执行计划中出现：VIEW VIEW_3 ,说明未做视图合并。

--带有DISTINCT的视图做视图合并。
create view view_4 as select distinct cust_id,prod_id from sales; 

select  t1.cust_id,t1.cust_last_name 
from customers t1,products t2 ,view_4 t3 
where t1.cust_id=t3.cust_id 
and t2.prod_id=t3.prod_id
and t3.total>700 
and t2.prod_category='Hardware';

对于DISTINCT的复杂视图合并，即使oracle已经做了复杂视图合并，但所对应的执行计划中还是会显示
关键字"VIEW",并且NAME列不是显示视图的名称，而是为空。

在oracle数据库中，通过计算查询转换前后的成本来决定是否做查询转换，是受隐含参数
_OPTIMIZER_COST_BASED_TRANSFORMATION 控制的，这个隐含参数可以在session级和系统级
动态修改，默认值"LINEAR",表示在默认情况下这种通过查询转换前后的成本来决定是否
来做查询转换的特性已经被开启了。

5.总结
视图合并并不总是是最优的执行计划，要视图具体情况来看。
如果视图合并最优:/*+merge(view_1)*/
如果不做视图合并最优:/*+no_merge(view_1)*/