WITH+HInt MATERIALIZE 不见得有效

最新推荐文章于 2025-06-02 16:14:02 发布

原创最新推荐文章于 2025-06-02 16:14:02 发布 · 8.8k 阅读

0 ·

CC 4.0 BY-SA版权

Oracle 开发专栏收录该内容

129 篇文章

订阅专栏

本文探讨了Oracle数据库中使用WITH子句与MATERIALIZE提示来优化查询的方法，特别是在减少重复计算和提高查询效率方面的应用。适用于多次使用的子查询场景。

那个要多次调用才需要物化的。
只调用一次，物化没用
MATERIALIZE
语法：MATERIALIZE
描述：指示优化器将内联视图实体化————执行过程中会创建基于视图的临时表。

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;

下面有文章介绍其好处

http://www.itpub.net/thread-1443086-1-1.html
WITH子句与未公开HINT MATERIALIZE联合使用, 取代临时表

运行环境：
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.    相同子查询被多次使用。

参考page：
http://www.remote-dba.net/oracle ... with_sql_clause.htm
http://www.dba-oracle.com/t_materialize_sql_hint.htm

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

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

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

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

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