Vista | 软件评测 | 系统备份 | 优化 | 进程 | 聊天 | 病毒 | Linux | 黑客 | 防火墙 | 数据库 | Web开发 | Java | Word | 游戏 | 32位开发 | 移动开发

Oracle 9i 数据库WITH查询语法小议

发布时间：2006-10-13 07:40:00　来源：友佳学院网友评论 0 条

Oracle9i新增了WITH语法功能，可以将查询中的子查询命名，放到SELECT语句的最前面。

下面看一个简单的例子:

SQL> WITH2 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_K5 FROM OBJ O, 
SEG S6 WHERE O.OBJECT_NAME = S.SEGMENT_NAME (+)7 ;
OBJECT_NAME OBJECT_TYPE SIZE_KDAIJC_TEST TABLE 128P_TEST PROCEDURE 0IND_DAIJC_TEST_C1 INDEX 128

通过WITH语句定义了两个子查询SEG和OBJ，在随后的SELECT语句中可以直接对预定义的子查询进行查询。从上面的例子也可以看出，使用WITH语句，将一个包含聚集、外连接等操作SQL清晰的展现出来。

WITH定义的子查询不仅可以使查询语句更加简单、清晰，而且WITH定义的子查询还具有在SELECT语句的任意层均可见的特点。

即使是在WITH的定义层中，后定义的子查询都可以使用前面已经定义好的子查询:

SQL> WITH2 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*M8 15 23 120

利用WITH定义查询中出现多次的子查询还能带来性能提示。Oracle会对WITH进行性能优化，当需要多次访问WITH定义的子查询时，Oracle会将子查询的结果放到一个临时表中，避免同样的子查询多次执行，从而有效的减少了查询的IO数量。

看一个简单的例子，首先构造一张大表，现在要取出大表中ID最小、ID最大以及ID等于平均值的记录，看看普通写法和WITH语句的区别:

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_WITH2 WHERE ID IN 3 (4 SELECT MAX(ID) 
FROM T_WITH 5 UNION ALL6 SELECT MIN(ID) 
FROM T_WITH7 UNION ALL8 SELECT TRUNC(AVG(ID)) 
FROM T_WITH9 )
;ID NAME1 STANDARD50000 DBMS_BACKUP_RESTORE100000 
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 calls0 db block gets5529 consistent gets0 physical reads0 redo 
size543 bytes sent via SQL*Net to client385 bytes received via SQL*Net from 
client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts 
(disk)3 rows processed

为了避免第一次执行时物理读的影响，查询结果选取了SQL的第三次运行，物理读为0时的统计信息。

观察执行计划可以看到，先后对T_WITH表进行了4次全表扫描，并产生了5529个逻辑读。下面看看WITH语句的表现:

SQL> WITH2 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 AGG11 );ID NAME
---------- ------------------------------
1 STANDARD50000 DBMS_BACKUP_RESTORE100000 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 || 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662E_BF2EDF12 | 
1 | 13 || 13 | VIEW | | 1 | 13 || 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662E_BF2EDF12 
| 1 | 13 || 15 | TABLE ACCESS FULL | T_WITH | 112K| 3299K|
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("ID"="$nso_col_1")Note------ dynamic sampling used for this statement统计信息
----------------------------------------------------------
2 recursive calls8 db block gets2776 consistent gets1 physical reads648 redo 
size543 bytes sent via SQL*Net to client385 bytes received via SQL*Net from client2 
SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)3 rows processed

观察这次的执行计划，发现只对T_WITH表进行了两次全表扫描，而从逻辑读上也可以观察到，这次只产生了2776的逻辑读，正好是上面不使用WITH语句的一半。

通过分析执行计划，Oracle执行了WITH子查询一次，并将结果放到了临时表中，在随后对子查询的多次访问中，都从临时表中直接读取了数据，这应该也是那1个物理读的由来。

通过上面的例子可以看到，将子查询放到WITH语句中不仅可以简化查询语句的结构，对于子查询需要多次执行的情况，还有可能提示查询的性能。

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

SQL> SET AUTOT OFFSQL> SET TIMING OFFSQL> WITH SUBQ AS (SELECT 1 FROM DUAL)2 SELECT ID, 
NAME FROM T_WITH WHERE ID IN (SELECT * FROM SUBQ);ID NAME
---------- ------------------------------
1 STANDARDSQL> WITH SUBQ AS (SELECT 1 FROM DUAL)2 UPDATE T_WITH SET ID = 1 
WHERE ID IN (SELECT * FROM SUBQ);UPDATE T_WITH SET ID = 1 WHERE ID IN (SELECT * FROM SUBQ)
*第 2 行出现错误:ORA-00928: 缺失 SELECT 关键字SQL> WITH SUBQ AS (SELECT 1 FROM DUAL)2 DELETE 
T_WITH WHERE ID IN (SELECT * FROM SUBQ);DELETE T_WITH WHERE ID IN (SELECT * FROM SUBQ)
*第 2 行出现错误:ORA-00928: 缺失 SELECT 关键字

关于 Oracle 9i 数据库 WITH查询语法 WITH Oracle 的新闻

【评论】【收藏本文】【打印】【关闭】

上一篇文章：Hibernate二级缓存攻略

下一篇文章：在Oracle的网络结构中解决连接问题

已有 0 位对此新闻感兴趣的网友发表了看法

图文阅读推荐

Atlas快速入门之实战Atlas

.net页面间的参数传递简单实例

全站资源

阅读排行