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 已用
