Oraclei新增了WITH语法功能可以将查询中的子查询命名放到SELECT语句的最前面
下面看一个简单的例子:
SQL> WITH SEG AS (SELECT SEGMENT_NAME SUM(BYTES)/ K FROM USER_SEGMENTS GROUP BY SEGMENT_NAME) OBJ AS (SELECT OBJECT_NAME OBJECT_TYPE FROM USER_OBJECTS) SELECT OOBJECT_NAME OBJECT_TYPE NVL(SK ) SIZE_K FROM OBJ O SEG S WHERE OOBJECT_NAME = SSEGMENT_NAME (+) ;OBJECT_NAME OBJECT_TYPE SIZE_KDAIJC_TEST TABLE P_TEST PROCEDURE IND_DAIJC_TEST_C INDEX
通过WITH语句定义了两个子查询SEG和OBJ在随后的SELECT语句中可以直接对预定义的子查询进行查询从上面的例子也可以看出使用WITH语句将一个包含聚集外连接等操作SQL清晰的展现出来
WITH定义的子查询不仅可以使查询语句更加简单清晰而且WITH定义的子查询还具有在SELECT语句的任意层均可见的特点
即使是在WITH的定义层中后定义的子查询都可以使用前面已经定义好的子查询:
SQL> WITH Q AS (SELECT + S FROM DUAL) Q AS (SELECT * M FROM DUAL) Q AS (SELECT S M S + M S * M FROM Q Q) SELECT * FROM Q;S M S+M S*M
利用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 < ; 表已创建 SQL> SET TIMING ON SQL> SET AUTOT ON SQL> SELECT ID NAME FROM T_WITH WHERE ID IN ( SELECT MAX(ID) FROM T_WITH UNION ALL SELECT MIN(ID) FROM T_WITH UNION ALL SELECT TRUNC(AVG(ID)) FROM T_WITH );ID NAME STANDARD DBMS_BACKUP_RESTORE INITJVMAUX已用时间: : : 执行计划Plan hash value: | Id | Operation | Name | Rows | Bytes || | SELECT STATEMENT | | | ||* | HASH JOIN | | | || | VIEW | VW_NSO_ | | || | HASH UNIQUE | | | || | UNIONALL | | | || | SORT AGGREGATE | | | || | TABLE ACCESS FULL| T_WITH | K| K|| | SORT AGGREGATE | | | || | TABLE ACCESS FULL| T_WITH | K| K|| | SORT AGGREGATE | | | || | TABLE ACCESS FULL| T_WITH | K| K|| | TABLE ACCESS FULL | T_WITH | K| K|Predicate Information (identified by operation id): access(ID=$nso_col_)Note dynamic sampling used for this statement统计信息 recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processed
为了避免第一次执行时物理读的影响查询结果选取了SQL的第三次运行物理读为时的统计信息
观察执行计划可以看到先后对T_WITH表进行了次全表扫描并产生了个逻辑读下面看看WITH语句的表现:
SQL> WITH AGG AS (SELECT MAX(ID) MAX MIN(ID) MIN TRUNC(AVG(ID)) AVG FROM T_WITH) SELECT ID NAME FROM T_WITH WHERE ID IN ( SELECT MAX FROM AGG UNION ALL SELECT MIN FROM AGG UNION ALL SELECT AVG FROM AGG );ID NAME STANDARD DBMS_BACKUP_RESTORE INITJVMAUX已用时间: : : 执行计划Plan hash value: | Id | Operation | Name | Rows | Bytes || | SELECT STATEMENT | | | || | TEMP TABLE TRANSFORMATION | | | || | LOAD AS SELECT | T_WITH | | || | SORT AGGREGATE | | | || | TABLE ACCESS FULL | T_WITH | K| K||* | HASH JOIN | | | || | VIEW | VW_NSO_ | | || | HASH UNIQUE | | | || | UNIONALL | | | || | VIEW | | | || | TABLE ACCESS FULL | SYS_TEMP_FDDE_BFEDF | | || | VIEW | | | || | TABLE ACCESS FULL | SYS_TEMP_FDDE_BFEDF | | || | VIEW | | | || | TABLE ACCESS FULL | SYS_TEMP_FDDE_BFEDF | | || | TABLE ACCESS FULL | T_WITH | K| K|Predicate Information (identified by operation id): access(ID=$nso_col_)Note dynamic sampling used for this statement统计信息 recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processed
观察这次的执行计划发现只对T_WITH表进行了两次全表扫描而从逻辑读上也可以观察到这次只产生了的逻辑读正好是上面不使用WITH语句的一半
通过分析执行计划Oracle执行了WITH子查询一次并将结果放到了临时表中在随后对子查询的多次访问中都从临时表中直接读取了数据这应该也是那个物理读的由来
通过上面的例子可以看到将子查询放到WITH语句中不仅可以简化查询语句的结构对于子查询需要多次执行的情况还有可能提示查询的性能
可惜的是WITH语句只能用在SELECT语句中UPDATE和DELETE语句不支持WITH语法:
SQL> SET AUTOT OFFSQL> SET TIMING OFFSQL> WITH SUBQ AS (SELECT FROM DUAL) SELECT ID NAME FROM T_WITH WHERE ID IN (SELECT * FROM SUBQ);ID NAME STANDARDSQL> WITH SUBQ AS (SELECT FROM DUAL) UPDATE T_WITH SET ID = WHERE ID IN (SELECT * FROM SUBQ);UPDATE T_WITH SET ID = WHERE ID IN (SELECT * FROM SUBQ)*第 行出现错误:ORA: 缺失 SELECT 关键字SQL> WITH SUBQ AS (SELECT FROM DUAL) DELETE T_WITH WHERE ID IN (SELECT * FROM SUBQ);DELETE T_WITH WHERE ID IN (SELECT * FROM SUBQ)*第 行出现错误: ORA: 缺失 SELECT 关键字
