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_K
DAIJC_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 OFF
SQL> SET TIMING OFF
SQL> WITH SUBQ AS (SELECT FROM DUAL)
SELECT ID NAME FROM T_WITH WHERE ID IN (SELECT * FROM SUBQ);
ID NAME
STANDARD
SQL> 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 關鍵字
From:http://tw.wingwit.com/Article/program/Oracle/201311/18491.html
