SQL語句是關係型資料庫最直接也是最普遍的資料訪問、操作方式。應該說，RDBMS的成功因素之中，SQL語言標準的推廣有不可磨滅的功勞。Oracle、SQL Server和DB2等主流資料庫都是在標準SQL的基礎上，加以個性化擴充，實現高效的資料庫應用。

with as是Oracle SQL語系裡面經常使用到的一種語句結構。特別是在一些資料倉儲挖掘系統中，SQL語句結構和巢狀結構是很複雜的。with as可以幫助我們簡化語句結構，提高語句可讀性，另外還可以提升語句執行計劃控制性。下面我們透過五個SQL語句，分析其執行計劃看with as特性。

1、環境介紹

我們選擇Oracle 10gR2進行實驗，實驗schema為scott。

SQL> select * from v$version;

BANNER

-----------------------------

Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

SQL> select table_name from user_tables;

TABLE_NAME

------------------------------

DEPT

EMP

BONUS

SALGRADE

從語法上看，with as就是將SQL語句中單獨的子查詢語句提取出來，作為一個單獨的命名進行組織。在原來的語句中，就可以使用新的別名來替代。

SQL> select count(*)

2 from (select emp.empno empno, dept.dname from emp, dept where emp.deptno=dept.deptno);

COUNT(*)

----------

14

SQL> with a as (select emp.empno empno, dept.dname from emp, dept where emp.deptno=dept.deptno)

2 select count(*)

3 from a;

COUNT(*)

----------

14

2、語句分析

在很多文件中，都介紹了with as的用途中有一條是資料臨時表化。當我們在一個SQL中包括子查詢的時候，Oracle可能不會按照“子查詢=》主查詢”的順序執行語句的。SQL Transformation過程中有一個子查詢展開（subquery unnested）。一般的子查詢都會被展開，作為一系列的join動作出現在執行計劃中。with as所謂的資料臨時表化，就是支援將subquery先進行執行，形成記憶體中的臨時表，再進行後續操作。

下面是我們第一個SQL語句，是一個子查詢與一個資料表的連線。

SQL> explain plan for with a as (select emp.empno, emp.ename, dept.loc, emp.job from emp, dept where emp.deptno=dept.deptno)

2 select *

3 from a m, dept n

4 where m.loc=n.loc;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3732937302

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 728 | 10 (20)| 0

|* 1 | HASH JOIN | | 14 | 728 | 10 (20)| 0

| 2 | MERGE JOIN | | 14 | 448 | 6 (17)| 0

| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 44 | 2 (0)| 0

| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 0

|* 5 | SORT JOIN | | 14 | 294 | 4 (25)| 0

| 6 | TABLE ACCESS FULL | EMP | 14 | 294 | 3 (0)| 0

| 7 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 0

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("DEPT"."LOC"="N"."LOC")

5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

21 rows selected

上面SQL中，將子查詢整理在with as中，以a的別名出現在系統中。在主查詢裡面，a與dept進行連線。從執行計劃看，雖然有用with as組織，Oracle最佳化器CBO還是將子查詢展開了，作為一系列的join操作。由此：在單次使用with as的情況下，臨時表預執行動作是不會進行的。

下面語句，我們同樣抽取a，主查詢中兩次使用a查詢結果。

SQL> explain plan for with a as (select emp.empno, emp.ename, dept.loc, emp.job from emp, dept where emp.deptno=dept.deptno)

2 select *

3 from a m, a n

4 where m.loc=n.loc;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 202182242

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 49 |

| 1 | TEMP TABLE TRANSFORMATION | | |

| 2 | LOAD AS SELECT | | |

| 3 | MERGE JOIN | | 14 |

| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 |

| 5 | INDEX FULL SCAN | PK_DEPT | 4 |

|* 6 | SORT JOIN | | 14 |

| 7 | TABLE ACCESS FULL | EMP | 14 |

|* 8 | HASH JOIN | | 49 |

| 9 | VIEW | | 14 |

| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_207360D8 | 14 |

| 11 | VIEW | | 14 |

| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_207360D8 | 14 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

8 - access("M"."LOC"="N"."LOC")

26 rows selected

執行計劃出現了變化。最典型的是在之前，使用Temp Table Transformation，將子查詢進行預載入，形成記憶體中的SYS_TEMP_0FD9D6605_207360D8臨時表。之後，對這個臨時表進行join連線操作。這也就是我們經常看到的對於with as預載入的現象。對比之前的實驗，我們可以知道：只有在主查詢中超過一次使用with as中的子查詢結構，Oracle才會“自動”的進行臨時表預載入。

這樣的處理也是有道理的。子查詢和查詢巢狀主要是便於可讀性提升上，絕大多數的子查詢和巢狀，都可以透過一系列的連線來完成。而資料庫系統是擅長連線操作的。所以，從CBO角度看，大多數子查詢被開啟，構成系列的連線動作。

此外，子查詢單獨執行還存在臨時空間佔用的問題。如果進行子查詢單獨執行，臨時結果的快取要佔用額外的空間，成本消耗相對較高。

但是如果在主查詢中多次使用with as確定的別名，情況就不一樣了。快取一部分資料，要比重新執行一遍或者多次連線子查詢資料表要強得多。所以，當多次進行with as子迴圈檢索的時候，Oracle會自動的進行臨時表化。

下面我們看第三個SQL語句。

SQL> explain plan for with a as (select /*+MATERIALIZE*/emp.empno, emp.ename, dept.loc, emp.job from emp, dept where

emp.deptno=dept.deptno)

2 select *

3 from a m, dept n

4 where m.loc=n.loc;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2532848790

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 |

| 1 | TEMP TABLE TRANSFORMATION | | |

| 3 | MERGE JOIN | | 14 |

| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 |

| 5 | INDEX FULL SCAN | PK_DEPT | 4 |

|* 6 | SORT JOIN | | 14 |

| 7 | TABLE ACCESS FULL | EMP | 14 |

|* 8 | HASH JOIN | | 14 |

| 9 | TABLE ACCESS FULL | DEPT | 4 |

| 10 | VIEW | | 14 |

| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6606_207360D8 | 14 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

8 - access("M"."LOC"="N"."LOC")

25 rows selected

這個SQL中，我們使用了with as，在主查詢中只引用了一次a。但是，在執行計劃中還是出現了臨時表載入計算動作。區別就在於在子查詢中使用了materialize的hint。

with as和materialize是經常匹配出現的。如果with as不滿足臨時表載入動作條件（引用兩次），但是使用了materialize標記的話，Oracle還是會生成對應的臨時表載入執行計劃的。

如果不使用with as子句，而是直接使用materialize hint的話，會不會也出現臨時表載入動作呢？

SQL> explain plan for select *

2 from (select /*+MATERIALIZE*/emp.empno, emp.ename, dept.loc, emp.job from emp, dept where emp.deptno=dept.deptno) m,

3 (select /*+MATERIALIZE*/emp.empno, emp.ename, dept.loc, emp.job from emp, dept where emp.deptno=dept.deptno) n

4 where m.loc=n.loc;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2298815876

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 49 | 3136 | 13 (16)|

|* 1 | HASH JOIN | | 49 | 3136 | 13 (16)|

|* 2 | HASH JOIN | | 14 | 602 | 10 (20)|

| 3 | MERGE JOIN | | 14 | 448 | 6 (17)|

| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 44 | 2 (0)|

| 5 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)|

|* 6 | SORT JOIN | | 14 | 294 | 4 (25)|

| 7 | TABLE ACCESS FULL | EMP | 14 | 294 | 3 (0)|

| 8 | TABLE ACCESS FULL | DEPT | 4 | 44 | 3 (0)|

| 9 | TABLE ACCESS FULL | EMP | 14 | 294 | 3 (0)|

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

2 - access("DEPT"."LOC"="DEPT"."LOC")

6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

24 rows selected

單獨使用materialize hint，是不會起到臨時表作用的。with as可以支援多個別名塊建立動作，看第五個SQL。

SQL> explain plan for with a as (select /*+MATERIALIZE*/emp.empno, emp.ename, dept.loc, emp.job from emp, dept where

emp.deptno=dept.deptno),

2 b as (select /*+MATERIALIZE*/emp.empno, emp.ename, dept.loc, emp.job from emp, dept where

emp.deptno=dept.deptno)

3 select *

4 from a m,

5 b n

6 where m.loc=n.loc;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1083325232

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 49 |

| 1 | TEMP TABLE TRANSFORMATION | | |

| 2 | LOAD AS SELECT | | |

| 3 | MERGE JOIN | | 14 |

| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 |

| 5 | INDEX FULL SCAN | PK_DEPT | 4 |

|* 6 | SORT JOIN | | 14 |

| 7 | TABLE ACCESS FULL | EMP | 14 |

| 8 | LOAD AS SELECT | | |

| 9 | MERGE JOIN | | 14 |

| 10 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 |

| 11 | INDEX FULL SCAN | PK_DEPT | 4 |

|* 12 | SORT JOIN | | 14 |

| 13 | TABLE ACCESS FULL | EMP | 14 |

|* 14 | HASH JOIN | | 49 |

| 15 | VIEW | | 14 |

| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6607_207360D8 | 14 |

| 17 | VIEW | | 14 |

| 18 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6608_207360D8 | 14 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

12 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

14 - access("M"."LOC"="N"."LOC")

34 rows selected

3、結論

綜合上面的討論，我們可以得到with as應用的條件和好處。總體上看，with as可以帶來兩方面的好處。第一是語句清晰度上，將原有的複雜子查詢內容拆解為若干語句塊，提高了可讀性。第二是對於自動臨時表，可以將子查詢資料先檢索出來，在記憶體中進行組織連線。

最後我們想討論一下對於subquery unnested特性。大多數情況下，我們認為Oracle這個特性是可以生成更好地執行計劃的。但是，在實踐中我們也的確發現過很多這樣的場景：子查詢速度很快、資料量也不大，但是連線之後速度變慢，結果是發現執行計劃中子查詢被開啟。

預設情況下，Oracle在估算多條件語句結果集數量的時候，是有“計算偏小”的特性的。也就是估算的結果集數量要小於實際數量，這個是由於預設統計量沒有考慮列相關性的原因。所以，大部分查詢展開都是被透過的。

如果出現這樣的情況，with as + materialize是一種強制進行子查詢先執行的好辦法。

從五個SQL看with as使用

相關文章