Oracle 9i 数据库WITH查询语法小议
2007-05-10 12:19:43 来源:WEB开发网核心提示: SQL> CREATE TABLE T_WITH AS SELECT ROWNUM ID, A.* FROM DBA_SOURCE A WHERE ROWNUM < 100001;表已创建。SQL> SET TIMING ONSQL> SET AUTOT ONSQL
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
- ››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修改表的两种方式
赞助商链接