看cost-based oracle fundamentals一书的时候，看到使用 materialize 提示。之前从来没用过这个提示，于是google一把。

使用范围：用于with clause。

作用：针对with中的查询，先建立一个 global temporary table，之后查询这个临时表。执行某些sql的时候，特别是多次使用with子查询的时候，会提升速度。

与之相对还有一个inline hint, Oracle指示所有的部分查询不使用TEMP TABLE TRANSFORMATION。

SQL> CREATE TABLE t0103_1 NOLOGGING AS SELECT * FROM dba_objects where ROWNUM<10000;

Table created.

SQL> with tmp as (select * from t0103) select * from tmp;

Execution Plan

----------------------------------------------------------

Plan hash value: 2929628763

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 999 | 87912 | 174 (0)| 00:00:03 |

| 1 | TABLE ACCESS FULL| T0103 | 999 | 87912 | 174 (0)| 00:00:03 |

---------------------------------------------------------------------------

SQL> with tmp as (select /*+ materialize */ * from t0103) select * from tmp;

Execution Plan

----------------------------------------------------------

Plan hash value: 877440232

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 999 | 201K| 178 (0)| 00:00:03 |

| 1 | TEMP TABLE TRANSFORMATION | | | | | |

| 3 | TABLE ACCESS FULL | T0103 | 999 | 87912 | 174 (0)| 00:00:03 |

| 4 | VIEW | | 999 | 201K| 4 (0)| 00:00:01 |

| 5 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_1690D1 | 999 | 87912 | 4 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------------

参考： http://dioncho.wordpress.com/tag/inline/

http://caohong286.iteye.com/blog/1528711

ITPub博客

materialize hint, inline hint