Oracle 9i 数据库WITH查询语法小议
2007-05-10 12:19:43 来源:WEB开发网核心提示: 为了避免第一次执行时物理读的影响,查询结果选取了SQL的第三次运行,Oracle 9i 数据库WITH查询语法小议(3),物理读为0时的统计信息,观察执行计划可以看到,还有可能提示查询的性能,可惜的是,先后对T_WITH表进行了4次全表扫描,并产生了5529个逻辑读
为了避免第一次执行时物理读的影响,查询结果选取了SQL的第三次运行,物理读为0时的统计信息。
观察执行计划可以看到,先后对T_WITH表进行了4次全表扫描,并产生了5529个逻辑读。下面看看WITH语句的表现:
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 |
| 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 calls
8 db block gets
2776 consistent gets
1 physical reads
648 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
观察这次的执行计划,发现只对T_WITH表进行了两次全表扫描,而从逻辑读上也可以观察到,这次只产生了2776的逻辑读,正好是上面不使用WITH语句的一半。
通过分析执行计划,Oracle执行了WITH子查询一次,并将结果放到了临时表中,在随后对子查询的多次访问中,都从临时表中直接读取了数据,这应该也是那1个物理读的由来。
通过上面的例子可以看到,将子查询放到WITH语句中不仅可以简化查询语句的结构,对于子查询需要多次执行的情况,还有可能提示查询的性能。
可惜的是,WITH语句只能用在SELECT语句中,UPDATE和DELETE语句不支持WITH语法:
SQL> SET AUTOT OFF
SQL> SET TIMING OFF
SQL> WITH SUBQ AS (SELECT 1 FROM DUAL)
2 SELECT ID, NAME FROM T_WITH WHERE ID IN (SELECT * FROM SUBQ);
ID NAME
---------- ------------------------------
1 STANDARD
SQL> 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 中 UPDATE nowait 的使用方法
- ››Oracle ORA-12560解决方法
- ››Oracle 10g RAC 常用维护命令
- ››Oracle如何在ASM中定位文件的分布
- ››Oracle的DBMS_RANDOM.STRING 的用法
- ››oracle 外部表导入时间日期类型数据,多字段导入
- ››Oracle中查找重复记录
- ››oracle修改用户登录密码
- ››Oracle创建删除用户、角色、表空间、导入导出等命...
- ››Oracle中登陆时报ORA-28000: the account is lock...
- ››Oracle数据库在配置文件中更改最大连接数
- ››Oracle中在pl/sql developer修改表的两种方式
赞助商链接