Oracle9i数据库WITH查询语法小议是什么?
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...全部
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 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时的统计信息。
。收起