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语法:

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10455206/viewspace-1031411/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10455206/viewspace-1031411/