WITH+HInt MATERIALIZE不见得有效

那个要多次调用才需要物化的。

只调用一次，物化没用

MATERIALIZE

语法：MATERIALIZE

描述：指示优化器将内联视图实体化————执行过程中会创建基于视图的临时表。

[sql] 
with dd as (select  object_type,owner,object_Id    from dba_objects)  
select OBJECT_TYPE, count(*)  
  from dd  
 group by OBJECT_TYPE  
union all  
select owner, count(*) from dd group by owner;  
  
  
with dd as (select /*+materialize */object_type,owner,object_Id from dba_objects)  
select OBJECT_TYPE, count(*)  
  from dd  
 group by OBJECT_TYPE  
union all  
select owner, count(*) from dd group by owner;

运行环境：

ORACLE, Release 11.2.0.1.0.

描述：

在SQL-99中，查询语句增加了WITH子句，可以使相同的子查询在一个sql中只执行一次，另外也可以把复杂的查询简化、增加可读性。

另外，我们知道利用ORACLE的GTT(GLOBAL TEMPORARY)全局临时表、MATERIALIZE VIEW物化视图来提高查询的效率。

其实我们可以通过WITH与未公开的提示（HINT）MATERIALIZE联合使用，在子查询结果记录数不是太多的情况下，取代GTT和物化视图。

,,,,,,,,

执行结果，花费时间减少一半。

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

结论：

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

1. 当次查询的结果记录数不是太多的情况下，如果结果记录集比较庞大，还是推荐使用GTT，因为那里面可以指定索引等。

2. 查询值和查询过滤条件中如果含有重复执行的表达式，可以用此方式减少一次计算。

3. 相同子查询被多次使用。

我看了下感觉不是加了物化提示才减少时间或许是 SELECT /*+ NO_MERGE(V_TMP) FULL(V_TMP) */ * 这提示减少时间吧

深圳-道道(726442711) 17:25:53

加提示之后，就是临时表的形式了

那个要多次调用才需要物化的。

只调用一次，物化没用

MATERIALIZE

语法：MATERIALIZE

描述：指示优化器将内联视图实体化————执行过程中会创建基于视图的临时表。

可以防止外层谓词内推和内联视图展开.

WITH 语句本身可以共享结果在同一条语句内增加物化提示是否是多余之举而且是官方非公开的