oracle9i新增了with语法功能，可以将查询中的子查询命名，放到select语句的最前面。
oracle9i新增了with语法功能，可以将查询中的子查询命名，放到select语句的最前面。
下面看一个简单的例子:
sql> with
2 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_k
5 from obj o, seg s
6 where o.object_name = s.segment_name (+)
7 ;
object_name object_type size_k
------------------------------ ------------------- ----------
daijc_test table 128
p_test procedure 0
ind_daijc_test_c1 index 128 |
通过with语句定义了两个子查询seg和obj，在随后的select语句中可以直接对预定义的子查询进行查询。从上面的例子也可以看出，使用with语句，将一个包含聚集、外连接等操作sql清晰的展现出来。
with定义的子查询不仅可以使查询语句更加简单、清晰，而且with定义的子查询还具有在select语句的任意层均可见的特点。
即使是在with的定义层中，后定义的子查询都可以使用前面已经定义好的子查询:
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语句的区别:
| 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语句的表现:
|
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 关键字 |
闽公网安备 35060202000074号