Oracle9i数据库WITH查询语法小议
Oracle9i新增了WITH语法功能，可以将查询中的子查询命名，放到SELECT语句的最前面。下面看一个简单的例子:
|
通过WITH语句定义了两个子查询SEG和OBJ，在随后的SELECT语句中可以直接对预定义的子查询进行查询。从上面的例子也可以看出，使用WITH语句，将一个包含聚集、外连接等操作SQL清晰的展现出来。
WITH定义的子查询不仅可以使查询语句更加简单、清晰，而且WITH定义的子查询还具有在SELECT语句的任意层均可见的特点。
即使是在WITH的定义层中，后定义的子查询都可以使用前面已经定义好的子查询:
bordercolorlight = "black" bordercolordark = "#FFFFFF" align="center">
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数量。
看一个简单的例子，首先构造一张大表，现在要取出大表中ID最小、ID最大以及ID等于平均值的记录，看看普通写法和WITH语句的区别:
bordercolorlight = "black" bordercolordark = "#FFFFFF" align="center">
SQL> CREATE TABLE T_WITH AS SELECT ROWNUM ID, A.* FROM DBA_SOURCE A WHERE ROWNUM < 100001;
表已创建。
SQL> SET TIMING ON
SQL> SET AUTOT ON
SQL> SELECT ID, NAME FROM T_WITH
2 WHERE ID IN
3 (
4 SELECT MAX(ID) FROM T_WITH
5 UNION ALL
6 SELECT MIN(ID) FROM T_WITH
7 UNION ALL
8 SELECT TRUNC(AVG(ID)) FROM T_WITH
9 );
ID NAME
1 STANDARD
50000 DBMS_BACKUP_RESTORE
100000 INITJVMAUX
已用时间: 00: 00: 00.09
执行计划
Plan hash value: 647530712
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 129 |
|* 1 | HASH JOIN | | 3 | 129 |
| 2 | VIEW | VW_NSO_1 | 3 | 39 |
| 3 | HASH UNIQUE | | 3 | 39 |
| 4 | UNION-ALL | | | |
| 5 | SORT AGGREGATE | | 1 | 13 |
| 6 | TABLE ACCESS FULL| T_WITH | 112K| 1429K|
| 7 | SORT AGGREGATE | | 1 | 13 |
| 8 | TABLE ACCESS FULL| T_WITH | 112K| 1429K|
| 9 | SORT AGGREGATE | | 1 | 13 |
| 10 | TABLE ACCESS FULL| T_WITH | 112K| 1429K|
| 11 | TABLE ACCESS FULL | T_WITH | 112K| 3299K|
-----------------------------------------------------------
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
5529 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的第三次运行，物理读为0时的统计信息。
观察执行计划可以看到，先后对T_WITH表进行了4次全表扫描，并产生了5529个逻辑读。下面看看WITH语句的表现:
bordercolorlight = "black" bordercolordark = "#FFFFFF" align="center">
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 );
ID NAME
---------- ------------------------------
1 STANDARD
50000 DBMS_BACKUP_RESTORE
100000 INITJVMAUX
已用时间: 00: 00: 00.07
执行计划
----------------------------------------------------------
Plan hash value: 1033356310
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 129 |
| 1 | TEMP TABLE TRANSFORMATION | | | |
| 2 | LOAD AS SELECT | T_WITH | | |
| 3 | SORT AGGREGATE | | 1 | 13 |
| 4 | TABLE ACCESS FULL | T_WITH | 112K| 1429K|
|* 5 | HASH JOIN | | 3 | 129 |
| 6 | VIEW | VW_NSO_1 | 3 | 39 |
| 7 | HASH UNIQUE | | 3 | 39 |
| 8 | UNION-ALL | | | |
| 9 | VIEW | | 1 | 13 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662E_BF2EDF12 | 1 | 13 |
| 11 | VIEW | | 1 | 13 |
- 电脑通通透
- 玩转网络
- IE/注册表
- DOS/Win9x
- Windows Xp
- Windows 2000
- Windows 2003
- Windows Vista
- Windows 2008
- Windows7
- Unix/Linux
- 苹果机Mac OS
- windows8
- 安卓/Android
- Windows10
如果你遇到操作系统难题：
请访问www.zzzyk.com 试试CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络，
