ORACLE数据库除了可以保存永久表外，还可以建立临时表temporary tables。这些临时表用来保存一个会话SESSION的数据，或者保存在一个事务中需要的数据。当会话退出或者用户提交commit和回滚rollback事务的时候，临时表的数据自动清空，但是临时表的结构以及元数据还存储在用户的数据字典中。

1. 会话级临时表

会话级临时表是指临时表中的数据只在会话生命周期之中存在，当用户退出会话结束的时候，Oracle自动清除临时表中数据。

Create Global Temporary Table Table_Name(Col1 Type1,Col2 Type2...)On Commit Preserve Rows;

2.事务级临时表

事务级临时表是指临时表中的数据只在事务生命周期中存在。

Create Global Temporary Table Table_Name(Col1 Type1,Col2 Type2...)On Commit Delete Rows;

3. With Clause

with as在查询的时候建立临时表，数据是写入了内存中，在处理逻辑复杂且数据量不是很大的业务时，我们可以采取的这样的方法来提高sql的性能，并且也降低sql的复杂性，让逻辑更加的清晰，方便维护。

增加了SQL的易读性，如果构造了多个子查询，结构会更清晰；更重要的是：“一次分析，多次使用”，这也是为什么会提供性能的地方，达到了“少读”的目标。

如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个

全局临时表里。很多查询通过这种方法都可以提高速度。

with as语法–针对一个别名with tmp as (select * from tb_name)–针对多个别名withtmp as (select * from tb_name),tmp2 as (select * from tb_name2),tmp3 as (select * from tb_name3),.....

应用场景

用 with as 将一个子查询独立出来：一般来说，如果这个子查询定义的表名被调用2次及2次以上，那么CBO就会把这个子查询返回的数据放入临时表（对应执行计划中的 SYS_TEMP_XXX）；如果只被调用1次，则不会。

一般情况下，如果在 with as 中的子查询返回结果集很大，且调用该子查询不走索引时，加 materialize 能极大提升整体性能；如果在 with as 中的子查询返回结果集很小，且调用该子查询能走索引时，加 materialize 反而会降低整体性能，这种情况下，为稳定性能，可使用 inline 抑制其转换为临时表。

格式

with tmp as (select /*+ materialize */ * from tb_name)或者with tmp as (select /*+ inline*/ * from tb_name)

参考资料

下面是搜索到的英文文档资料

About Oracle WITH clause

Starting in Oracle9i release 2 we see an incorporation of the SQL-99 “WITH clause”, a tool for materializing subqueries to save Oracle from having to re-compute them multiple times.

The SQL “WITH clause” is very similar to the use of Global temporary tables (GTT), a technique that is often used to improve query speed for complex subqueries. Here are some important notes about the Oracle “WITH clause”:

• The SQL “WITH clause” only works on Oracle 9i release 2 and beyond.

• Formally, the “WITH clause” is called subquery factoring

• The SQL “WITH clause” is used when a subquery is executed multiple times

• Also useful for recursive queries (SQL-99, but not Oracle SQL)

To keep it simple, the following example only references the aggregations once, where the SQL “WITH clause” is normally used when an aggregation is referenced multiple times in a query.

We can also use the SQL-99 “WITH clause” instead of temporary tables. The Oracle SQL “WITH clause” will compute the aggregation once, give it a name, and allow us to reference it (maybe multiple times), later in the query.

The SQL-99 “WITH clause” is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the “WITH clause” to start our SQL query, defining the aggregations, which can then be named in the main query as if they were “real” tables:

WITH

subquery_name

AS

(the aggregation SQL statement)

SELECT

(query naming subquery_name);

Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH clause”:

WITH

sum_sales AS

select /*+ materialize */

sum(quantity) all_sales from stores

number_stores AS

select /*+ materialize */

count(*) nbr_stores from stores

sales_by_store AS

select /*+ materialize */

store_name, sum(quantity) store_sales from

store natural join sales

SELECT

store_name

FROM

store,

sum_sales,

number_stores,

sales_by_store

where

store_sales > (all_sales / nbr_stores)

;

Note the use of the Oracle undocumented “materialize” hint in the “WITH clause”. The Oracle materialize hint is used to ensure that the Oracle cost-based optimizer materializes the temporary tables that are created inside the “WITH” clause. This is not necessary in Oracle10g, but it helps ensure that the tables are only created one time.

It should be noted that the “WITH clause” does not yet fully-functional within Oracle SQL and it does not yet support the use of “WITH clause” replacement for “CONNECT BY” when performing recursive queries.

To see how the “WITH clause” is used in ANSI SQL-99 syntax, here is an excerpt from Jonathan Gennick’s great work “Understanding the WITH Clause” showing the use of the SQL-99 “WITH clause” to traverse a recursive bill-of-materials hierarchy

The SQL-99 “WITH clause” is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the “WITH clause” to start our SQL query, defining the aggregations, which can then be named in the main query as if they were “real” tables:

WITH

subquery_name

AS

(the aggregation SQL statement)

SELECT

(query naming subquery_name);

Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH” clause”: