熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle9i數據庫WITH查詢語法小議

2013-11-13 22:12:48  來源: Oracle 

  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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.