Oracle 9i 數(shù)據(jù)庫(kù)WITH查詢語(yǔ)法小議

2024-08-29 13:50:29

字體：大中小

供稿：網(wǎng)友

Oracle9i新增了WITH語(yǔ)法功能，可以將查詢中的子查詢命名，放到SELECT語(yǔ)句的最前面。下面看一個(gè)簡(jiǎn)單的例子:

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

通過(guò)WITH語(yǔ)句定義了兩個(gè)子查詢SEG和OBJ，在隨后的SELECT語(yǔ)句中可以直接對(duì)預(yù)定義的子查詢進(jìn)行查詢。從上面的例子也可以看出，使用WITH語(yǔ)句，將一個(gè)包含聚集、外連接等操作SQL清楚的展現(xiàn)出來(lái)。 WITH定義的子查詢不僅可以使查詢語(yǔ)句更加簡(jiǎn)單、清楚，而且WITH定義的子查詢還具有在SELECT語(yǔ)句的任意層均可見(jiàn)的特點(diǎn)。即使是在WITH的定義層中，后定義的子查詢都可以使用前面已經(jīng)定義好的子查詢:

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定義查詢中出現(xiàn)多次的子查詢還能帶來(lái)性能提示。Oracle會(huì)對(duì)WITH進(jìn)行性能優(yōu)化，當(dāng)需要多次訪問(wèn)WITH定義的子查詢時(shí)，Oracle會(huì)將子查詢的結(jié)果放到一個(gè)臨時(shí)表中，避免同樣的子查詢多次執(zhí)行，從而有效的減少了查詢的IO數(shù)量。看一個(gè)簡(jiǎn)單的例子，首先構(gòu)造一張大表，現(xiàn)在要取出大表中ID最小、ID最大以及ID等于平均值的記錄，看看普通寫(xiě)法和WITH語(yǔ)句的區(qū)別:

SQL> CREATE TABLE T_WITH AS SELECT ROWNUM ID, A.* FROM DBA_SOURCE A WHERE ROWNUM < 100001; 表已創(chuàng)建。 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已用時(shí)間: 00: 00: 00.09執(zhí)行計(jì)劃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統(tǒng)計(jì)信息----------------------------------------------------------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

為了避免第一次執(zhí)行時(shí)物理讀的影響，查詢結(jié)果選取了SQL的第三次運(yùn)行，物理讀為0時(shí)的統(tǒng)計(jì)信息。觀察執(zhí)行計(jì)劃可以看到，先后對(duì)T_WITH表進(jìn)行了4次全表掃描，并產(chǎn)生了5529個(gè)邏輯讀。下面看看WITH語(yǔ)句的表現(xiàn):

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已用時(shí)間: 00: 00: 00.07執(zhí)行計(jì)劃----------------------------------------------------------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統(tǒng)計(jì)信息----------------------------------------------------------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

觀察這次的執(zhí)行計(jì)劃，發(fā)現(xiàn)只對(duì)T_WITH表進(jìn)行了兩次全表掃描，而從邏輯讀上也可以觀察到，這次只產(chǎn)生了2776的邏輯讀，正好是上面不使用WITH語(yǔ)句的一半。通過(guò)分析執(zhí)行計(jì)劃，Oracle執(zhí)行了WITH子查詢一次，并將結(jié)果放到了臨時(shí)表中，在隨后對(duì)子查詢的多次訪問(wèn)中，都從臨時(shí)表中直接讀取了數(shù)據(jù)，這應(yīng)該也是那1個(gè)物理讀的由來(lái)。通過(guò)上面的例子可以看到，將子查詢放到WITH語(yǔ)句中不僅可以簡(jiǎn)化查詢語(yǔ)句的結(jié)構(gòu)，對(duì)于子查詢需要多次執(zhí)行的情況，還有可能提示查詢的性能。可惜的是，WITH語(yǔ)句只能用在SELECT語(yǔ)句中，UPDATE和DELETE語(yǔ)句不支持WITH語(yǔ)法:

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 行出現(xiàn)錯(cuò)誤: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 行出現(xiàn)錯(cuò)誤:ORA-00928: 缺失 SELECT 要害字

上一篇：Oracle 的數(shù)據(jù)庫(kù)的數(shù)據(jù)備份與恢復(fù)

下一篇：在Oracle的網(wǎng)絡(luò)結(jié)構(gòu)中解決連接問(wèn)題