with as与hint materialize的使用

WITH子句与未公开HINT MATERIALIZE联合使用, 取代临时表 


运行环境：
     ORACLE ,  Release 11.2.0.1.0.


描述：
在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.      相同子查询被多次使用。