關於oracle with table as 建立臨時表的用法示例以及使用with as 的優點

關於oracle with table as 建立臨時表的用法示例

1、with table as 相當於建個臨時表（用於一個語句中某些中間結果放在臨時表空間的SQL語句）， 9i 新增WITH語法，可以將查詢中的子查詢命名，放到SELECT語句的最前面。

語法就是
with tempname as (select ....)
select ...

例子：
with t as (select * from emp where depno=10)
select * from t where empno=xxx

with
wd as (select did,arg(salary) 平均工資 from work group by did),
em as (select emp.*,w.salary from emp left join work w on emp.eid = w.eid)
select * from wd,em where wd.did =em.did and wd.平均工資>em.salary;



2、何時被清除

臨時表不都是會話結束就自動被PGA清除嘛! 但with as臨時表是查詢完成後就被清除了！

註釋：

臨時表分類

ORACLE臨時表有兩種型別：會話級的臨時表和事務級的臨時表。

1）ON COMMIT DELETE ROWS

它是臨時表的預設引數，表示臨時表中的資料僅在事物過程（Transaction）中有效，當事物提交（COMMIT）後，臨時表的暫時段將被自動截斷（TRUNCATE），但是臨時表的結構 以及後設資料還儲存在使用者的資料字典中。如果臨時表完成它的使命後，最好刪除（drop命令）臨時表，否則會殘留很多臨時表的表結構和後設資料。

2）ON COMMIT PRESERVE ROWS

它表示臨時表的內容可以跨事物而存在，不過，當該會話結束時，臨時表的暫時段將隨著會話的結束而被丟棄，臨時表中的資料自然也就隨之丟棄。但是臨時表的結構以及後設資料還儲存在使用者的資料字典中。如果臨時表完成它的使命後，最好刪除（drop命令）臨時表，否則資料庫會殘留很多臨時表的表結構和後設資料。

23:48:58 SCOTT@orcl> with aa as(select * from dept)
23:57:58   2  select * from aa;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

已用時間:  00: 00: 00.12
23:58:06 SCOTT@orcl> select * from aa;
select * from aa
              *
第 1 行出現錯誤:
ORA-00942: 表或檢視不存在


已用時間:  00: 00: 00.02
23:58:14 SCOTT@orcl>

3、就這一功能來說，子查詢就可以達到啊，為什麼要用with呢？ 用with有什麼好處？
都能寫，但執行計劃不同的。當有多個相似子查詢的時候，用with寫公共部分，因為子查詢結果在記憶體臨時表中，執行效率當然就高啦~

4、問題：
有張表資料如下：
aaa 高
bbb 低
aaa 低
aaa 高
bbb 低
bbb 高
需要得到下列結果,
  高 低
aaa 2 1
bbb 1 2
問 SQL 語句怎麼寫？？

答案：
with tt as (
  select 'aaa' id, '高' value from dual union all
  select 'bbb' id, '低' value from dual union all
  select 'aaa' id, '低' value from dual union all
  select 'aaa' id, '高' value from dual union all
  select 'bbb' id, '低' value from dual union all
  select 'bbb' id, '高' value from dual)
SELECT id,
       COUNT(decode(VALUE, '高', 1)) 高,
       COUNT(decode(VALUE, '低', 1)) 低
  FROM tt
 GROUP BY id;
===================================================================
擴充套件：
Oracle9i新增WITH語法，可以將查詢中的子查詢命名，放到SELECT語句的最前面。

　　一個簡單的例子:

SQL> WITH
2 SEG AS (SELECT SEGMENT_NAME, SUM(BYTES)/1024 K FROM USER_SEGMENTS GROUP BY SEGMENT_NAME),
3 OBJ AS (SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS)
4 SELECT O.OBJECT_NAME, OBJECT_TYPE, NVL(S.K, 0) SIZE_K
5 FROM OBJ O, SEG S
6 WHERE O.OBJECT_NAME = S.SEGMENT_NAME (+)
7 ;
OBJECT_NAME OBJECT_TYPE SIZE_K
------------------------------ ------------------- ----------
DAIJC_TEST TABLE 128
P_TEST PROCEDURE 0
IND_DAIJC_TEST_C1 INDEX 128

　　透過WITH語句定義了兩個子查詢SEG和OBJ，在隨後的SELECT語句中可以直接對預定義的子查詢進行查詢。從上面的例子也可以看出，使用WITH語句，將一個包含聚集、外連線等操作SQL清晰的展現出來。

　　WITH定義的子查詢不僅可以使查詢語句更加簡單、清晰，而且WITH定義的子查詢還具有在SELECT語句的任意層均可見的特點。

　　即使是在WITH的定義層中，後定義的子查詢都可以使用前面已經定義好的子查詢:

SQL> WITH
2 Q1 AS (SELECT 3 + 5 S FROM DUAL),
3 Q2 AS (SELECT 3 * 5 M FROM DUAL),
4 Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)
5 SELECT * FROM Q3;
S M S+M S*M
---------- ---------- ---------- ----------
8 15 23 120

　　利用WITH定義查詢中出現多次的子查詢還能帶來效能提示。Oracle會對WITH進行效能最佳化，當需要多次訪問WITH定義的子查詢時，Oracle會將子查詢的結果放到一個臨時表中，避免同樣的子查詢多次執行，從而有效的減少了查詢的IO數量。

WITH能用在SELECT語句中，UPDATE和DELETE語句也是支援WITH語法的，只是需要版本支援:


=============================================================================
with
sql1 as (select to_char(a) s_name from test_tempa),
sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))
select * from sql1
union all
select * from sql2
union all
select 'no records' from dual
       where not exists (select s_name from sql1 where rownum=1)
       and not exists (select s_name from sql2 where rownum=1);

再舉個簡單的例子

with a as (select * from test)

select * from a;

其實就是把一大堆重複用到的SQL語句放在with as 裡面，取一個別名，後面的查詢就可以用它

這樣對於大批次的SQL語句起到一個最佳化的作用，而且清楚明瞭


這是搜尋到的英文文件資料(說得比較全,但是本人英文特菜,還沒具體瞭解到,希望各高手具體談談這個with
as 的好處)

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”: