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

2024-08-29 13:50:29

字体：大中小

来源：转载

供稿：网友

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 的数据库的数据备份与恢复

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

学习交流

如何查找有故障的配件

如何查找有故障的配件...

热门图片

猜你喜欢的新闻

猜你喜欢的关注