WITH的簡單用法

WITH的簡單用法[@more@]

WITH的簡單用法:
with a as (select 1 id,'a' name from dual
union
select 2 id,'b' name from dual
union
select 3 id,'c' name from dual
union
select 4 id,'d' name from dual
union
select 5 id,'e' name from dual
),
b AS(
SELECT 1 ID,33 cnt FROM dual
UNION
SELECT 4 ID,34 cnt FROM dual
)
SELECT id,a.name,b.cnt FROM a LEFT JOIN b USING(ID) ORDER BY ID;

WITH
Q1 AS (SELECT 3 + 5 S FROM DUAL),
Q2 AS (SELECT 3 * 5 M FROM DUAL),
Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)
SELECT * FROM Q3;

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

CREATE TABLE T_WITH AS SELECT ROWNUM ID, A.* FROM DBA_SOURCE A WHERE ROWNUM < 100001;

SQL> SELECT ID, NAME
2 FROM T_WITH
3 WHERE ID IN (SELECT MAX(ID)
4 FROM T_WITH
5 UNION ALL
6 SELECT MIN(ID)
7 FROM T_WITH
8 UNION ALL
9 SELECT TRUNC(AVG(ID)) FROM T_WITH);

已用時間: 00: 00: 00.09

執行計劃
----------------------------------------------------------
Plan hash value: 647530712

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 129 | 3083 (2)| 00:00:38 |
|* 1 | HASH JOIN | | 3 | 129 | 3083 (2)| 00:00:38 |
| 2 | VIEW | VW_NSO_1 | 3 | 39 | 2310 (1)| 00:00:28 |
| 3 | HASH UNIQUE | | 3 | 39 | 2310 (67)| 00:00:28 |
| 4 | UNION-ALL | | | | | |
| 5 | SORT AGGREGATE | | 1 | 13 | | |
| 6 | TABLE ACCESS FULL| T_WITH | 108K| 1382K| 770 (1)| 00:00:10 |
| 7 | SORT AGGREGATE | | 1 | 13 | | |
| 8 | TABLE ACCESS FULL| T_WITH | 108K| 1382K| 770 (1)| 00:00:10 |
| 9 | SORT AGGREGATE | | 1 | 13 | | |
| 10 | TABLE ACCESS FULL| T_WITH | 108K| 1382K| 770 (1)| 00:00:10 |
| 11 | TABLE ACCESS FULL | T_WITH | 108K| 3191K| 771 (2)| 00:00:10 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("ID"="$nso_col_1")

Note
-----
- dynamic sampling used for this statement

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
13949 consistent gets
0 physical reads
0 redo size
543 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed



SQL> WITH
2 AGG AS (SELECT MAX(ID) MAX, MIN(ID) MIN, TRUNC(AVG(ID)) AVG FROM T_WITH)
3 SELECT ID, NAME FROM T_WITH
4 WHERE ID IN
5 (
6 SELECT MAX FROM AGG
7 UNION ALL
8 SELECT MIN FROM AGG
9 UNION ALL
10 SELECT AVG FROM AGG
11 );

已用時間: 00: 00: 00.31

執行計劃
----------------------------------------------------------
Plan hash value: 3483471080

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 129 | 1549 (2)| 00:00:19 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | T_WITH | | | | |
| 3 | SORT AGGREGATE | | 1 | 13 | | |
| 4 | TABLE ACCESS FULL | T_WITH | 108K| 1382K| 770 (1)| 00:00:10 |
|* 5 | HASH JOIN | | 3 | 129 | 779 (2)| 00:00:10 |
| 6 | VIEW | VW_NSO_1 | 3 | 39 | 6 (0)| 00:00:01 |
| 7 | HASH UNIQUE | | 3 | 39 | 6 (67)| 00:00:01 |
| 8 | UNION-ALL | | | | | |
| 9 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6601_1E88D2 | 1 | 13 | 2 (0)| 00:00:01 |
| 11 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6601_1E88D2 | 1 | 13 | 2 (0)| 00:00:01 |
| 13 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6601_1E88D2 | 1 | 13 | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | T_WITH | 108K| 3191K| 771 (2)| 00:00:10 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("ID"="$nso_col_1")

Note
-----
- dynamic sampling used for this statement

統計資訊
----------------------------------------------------------
1146 recursive calls
10 db block gets
7551 consistent gets
2 physical reads
1432 redo size
543 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
39 sorts (memory)
0 sorts (disk)
3 rows processed



透過分析執行計劃，Oracle執行了WITH子查詢一次，並將結果放到了臨時表中，在隨後對子查詢的多次訪問中，都從臨時表中直接讀取了資料，這應該也是那1個物理讀的由來。

透過上面的例子可以看到，將子查詢放到WITH語句中不僅可以簡化查詢語句的結構，對於子查詢需要多次執行的情況，還有可能提示查詢的效能。

可惜的是，WITH語句只能用在SELECT語句中，UPDATE和DELETE語句不支援WITH語法: