從五個SQL看with as使用

---

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

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

| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| T

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

|   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

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

| Id  | Operation                      | Name                        | Rows  | B

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

|   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

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

| Id  | Operation                      | Name                        | Rows  | B

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

|   0 | SELECT STATEMENT               |                             |    14 |

|   1 |  TEMP TABLE TRANSFORMATION     |                             |       |

|   2 |   LOAD AS SELECT               | DEPT                        |       |

|   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

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

| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)|

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

|   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

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

| Id  | Operation                      | Name                        | Rows  | B

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

|   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是一種強制進行子查詢先執行的好辦法。

---