描述：
在SQL-99中，查询语句增加了WITH子句，可以使相同的子查询在一个sql中只执行一次，另外也可以把复杂的查询简化、增加可读性。
另外，我们知道利用ORACLE的GTT(GLOBAL TEMPORARY)全局临时表、MATERIALIZE VIEW物化视图来提高查询的效率。
其实我们可以通过WITH与未公开的提示（HINT）MATERIALIZE联合使用，在子查询结果记录数不是太多的情况下，取代GTT和物化视图。


说明：
下面是一个在实际系统中的sql：
SELECT 
rp.*,
rp.LAST_VALUE -  func_GSTl(rp.TYPE, rp.THISYEAR) AS LEFT_VALUE,
tm.RECORD_NAME,
tm.RECORD_UNIT         
FROM  TBL_RECORD_PERSONAL rp, TBL_M_TOTALRECORD tm
WHERE rp.RECORD_TYPE = tm.RECORD_TYPE
AND rp.LAST_VALUE -  func_GSTl(rp.TYPE, rp.THISYEAR) AS LEFT_VALUE >0


其中func_GSTl（）效率很低，内部要做很多隐含的查询。
此sql执行时，需要时间10秒左右，跟踪发现rp.LAST_VALUE -  func_GSTl(rp.TYPE, rp.THISYEAR)这个表达式被执行了两次，也就是生成查询值和查询条件中均执行了一次。


我们改写成如下方式：
WITH V_TMP AS {
SELECT 
rp.*,
rp.LAST_VALUE -  func_GSTl(rp.TYPE, rp.THISYEAR) AS LEFT_VALUE,
tm.RECORD_NAME,
tm.RECORD_UNIT         
FROM  TBL_RECORD_PERSONAL rp, TBL_M_TOTALRECORD tm
WHERE rp.RECORD_TYPE = tm.RECORD_TYPE
}
SELECT *
FROM V_TMP
WHERE LEFT_VALUE > 0;


效率上没有变化， oracle 的文档中说，在10g之后，with子句之后的子查询结果会被缓存下来，但是我们发现在11g下，实际上并没有把子查询结果保留下来，也许是自动优化的结果吧，oracle认为不可以缓存此部分内容。


在此情况下，通过hint materialize可以使with子句的查询结果缓冲下来，改写sql如下：
WITH V_TMP AS {
SELECT /*+ materialize*/
rp.*,
rp.LAST_VALUE -  func_GSTl(rp.TYPE, rp.THISYEAR) AS LEFT_VALUE,
tm.RECORD_NAME,
tm.RECORD_UNIT         
FROM  TBL_RECORD_PERSONAL rp, TBL_M_TOTALRECORD tm
WHERE rp.RECORD_TYPE = tm.RECORD_TYPE
}
SELECT /*+ NO_MERGE(V_TMP) FULL(V_TMP) */  *
FROM V_TMP
WHERE LEFT_VALUE > 0;


执行结果，花费时间减少一半。
materialize这个hint是oracle没有公开的提示，它指示ORACLE CBO在with子句内物化临时表。这在oracle10g之后是没有必要的，但是实际使用with时，如果发现没有被物化，可以用此hint来强制oracle作此处理，使此子查询（临时表）只被处理一次。


结论：
    在一定的情况下，我们可以用with与hint materialize结合的方式取代全局临时表和物化视图来提高查询效率，使用情况如下。


1.      当次查询的结果记录数不是太多的情况下，如果结果记录集比较庞大，还是推荐使用GTT，因为那里面可以指定索引等。
2.      查询值和查询过滤条件中如果含有重复执行的表达式，可以用此方式减少一次计算。
3.      相同子查询被多次使用。

Oracle Hint no_merge(merge)、no_unnest(unnest)的作用对象陷阱

Oracle的查询转换中有两个概念，子查询展开和视图合并。

关于两者的概念这里不赘述，可以看看崔华的《基于Oracle的SQL优化》这本书。

其中，no_unnest(unnest)这个Hint显式控制是否对子查询做展开，no_unnest表示该子查询不展开，unnest表示子查询展开。

而no_merge(merge)这个Hint显式控制是否进行视图合并，no_merge表示视图不合并，merge表示试图合并。

实际上最近又遇到对子查询使用no_unnest不生效的情况了，然后使用no_merge生效的情况。

还楞了好一会才反应过来，所以决定还是记录下。

崔华的《基于Oracle的SQL优化》的P642页介绍了merge和no_merge的使用对象和作用，

MERGE是针对单个目标视图的Hint，它的含义是让优化器对目标视图执行视图合并（View Merging）。

而P643页介绍了unnest和no_unnest的使用对象和作用，

UNNEST是针对子查询的Hint，它的含义是让优化器对目标SQL中的子查询执行子查询展开（Subquery Unnesting）。

举个例子，select * from a left join (select * from m where m.id=100) b on a.id=b.id。

这条sql使用no_unnest是不生效的，比如这就是有问题的：select * from a left join (select /*+ no_unnest */ * from m where m.id=100) b on a.id=b.id。

只能使用no_merge。

搞清楚这个问题只需要理解子查询的概念：当一个查询是另一个查询的条件时，称之为子查询。

一直潜意识就认为子查询就是嵌套的另外一些SELECT查询，实际上还要满足是另外一个查询的条件。

像上边的例子，显然不满足第二点。

而且实际上可以拿一个视图名字替换掉查询块内容而不会产生语法错误，比如变成select * from a left join view_name b on a.id=b.id那么这里就起到的是”视图“的作用，因此用no_merge。

像select * from a where a.id in (select b.id from b)则无法替换掉，否则产生语法错误。比如select * from a where a.id in view_name那就有问题了。

最后引用崔华的《基于Oracle的SQL优化》P337的一段话：

Oracle数据库里子查询前的where条件如果是如下这些条件之一，那么这种类型的目标SQL在满足了一定的条件后就可以做子查询展开：

• SINGLE-ROW（即=、<、>、<=、>=和<>）
• EXISTS
• NOT EXISTS
• IN
• NOT IN
• ANY
• ALL