關於查詢轉換的一些簡單分析(一)

在sql解析器中，在生成執行計劃的時候，會在多個執行計劃中選擇最優的計劃，在這個過程中，查詢轉換就是一個很重要的過程。
雖然最終的執行結果沒有變化，但是從最佳化器的角度來看，查詢轉換的結果會更好。
大體來說查詢轉換有以下幾種型別。
檢視合併
子查詢解巢狀
謂詞推進
物化檢視查詢重寫

比如
select *from emp where deptno in (select deptno from dept)
會在查詢轉換中轉換為下面的樣子.
select e.* from emp e,dept d where e.deptno=d.deptno;
從查詢轉換的角度來說，兩者的查詢效果是基本一致的。

--檢視合併
select *
from emp e,
     (select deptno from emp) s_view
where e.deptno=s_view.deptno;

70 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3638257876
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    71 |  2911 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    71 |  2911 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |    42 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="DEPTNO")

來比較一下是否檢視合併的結果。
select
*
from emp e,
     (select /*+no_merge*/ deptno from emp) s_view
where e.deptno=s_view.deptno

70 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2243607326
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    71 |  3621 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN          |      |    71 |  3621 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | EMP  |    14 |   532 |     2   (0)| 00:00:01 |
|   3 |   VIEW              |      |    14 |   182 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |    42 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="S_VIEW"."DEPTNO")

可以比較上面兩個查詢的時候，會發現第二個執行計劃的VIEW關鍵字表示，執行計劃是保持原樣的，會單獨處理檢視。
使用圓括號把子查詢括起來不一定在sql執行的時候會保證子查詢塊會單獨執行。
這個時候可以根據要求來選擇是否選擇檢視合併。

檢視合併在大體來說效能還是不錯的,不過在使用order by, 聚合函式，分析函式，group等等的時候，檢視合併的功能還是會受到限制或者無法啟用。
而且在試圖合併的功能受到限制的時候，如果硬要啟用檢視合併功能，可能查詢的結果會有一些變化，這個時候需要你來判斷是否確定要啟用檢視合併。
select e.ename,e.sal,v.avg_salary
from emp e,
(select deptno,avg(sal) avg_salary
from emp oe
group by deptno)v
where e.deptno=v.deptno and e.sal>1000;

12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 269884559
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    14 |   546 |     6  (34)| 00:00:01 |
|*  1 |  HASH JOIN           |      |    14 |   546 |     6  (34)| 00:00:01 |
|   2 |   VIEW               |      |     3 |    78 |     3  (34)| 00:00:01 |
|   3 |    HASH GROUP BY     |      |     3 |    21 |     3  (34)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP  |    14 |    98 |     2   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL  | EMP  |    13 |   169 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="V"."DEPTNO")
   5 - filter("E"."SAL">1000)

 

select /*+ merge(v)*/ e.ename,e.sal,v.avg_salary
from emp e,
(select deptno,avg(sal) avg_salary
from emp oe
group by deptno)v
where e.deptno=v.deptno and e.sal>1000;

12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4115741206
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    68 |  2176 |     6  (34)| 00:00:01 |
|   1 |  HASH GROUP BY      |      |    68 |  2176 |     6  (34)| 00:00:01 |
|*  2 |   HASH JOIN         |      |    68 |  2176 |     5  (20)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| EMP  |    13 |   325 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |    98 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("E"."DEPTNO"="DEPTNO")
   3 - filter("E"."SAL">1000)

 

--子查詢解巢狀
子查詢巢狀和檢視合併的不同之處在於檢視合併是在select xxx from的部分，而子查詢巢狀式在where xxxx的部分。

select *from emp where deptno in (select deptno from dept)
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   532 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPTNO" IS NOT NULL)

         
先來比較一下是否啟用子查詢巢狀的區別 ,如果不需要啟用子查詢巢狀，可以使用Hint no_unnest來引導。
select *from emp where deptno in (select /*+ no_unnest*/deptno from dept)
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1783302997
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     5 |   190 |     2   (0)| 00:00:01 |
|*  1 |  FILTER            |         |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   532 |     2   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     1 |     3 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "DEPT"
              WHERE "DEPTNO"=:B1))
   3 - access("DEPTNO"=:B1)

可以從謂語部分看出來，兩個的執行計劃還是有很大的不同，第二個執行計劃是未啟用子查詢解巢狀的形式。會在子查詢中走索引，然後透過exists子句來和外部查詢的deptno欄位連線。
而第一個執行計劃直接走了一個全表掃描，對於deptno的部分直接使用條件deptno is not null來過濾了。


select *from emp outer
where outer.sal>(select avg(inner.sal) from emp inner where inner.deptno=outer.deptno)
6 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1245077725
--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    64 |     6  (34)| 00:00:01 |
|*  1 |  HASH JOIN           |         |     1 |    64 |     6  (34)| 00:00:01 |
|   2 |   VIEW               | VW_SQ_1 |     3 |    78 |     3  (34)| 00:00:01 |
|   3 |    HASH GROUP BY     |         |     3 |    21 |     3  (34)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP     |    14 |    98 |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMP     |    14 |   532 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ITEM_1"="OUTER"."DEPTNO")
       filter("OUTER"."SAL">"AVG(INNER.SAL)")

這個例子中子查詢轉換為了一個內嵌式圖，然後與外部的查詢合併連線,相關列成為了連線條件，子查詢的剩餘部分成為了內嵌檢視。

sql的執行方式和下面的sql類似。
select *from emp outer,
(select inner.deptno,avg(inner.sal) avg_sal from emp inner group by inner.deptno)inner where inner.deptno=outer.deptno
and outer.sal>inner.avg_sal;

6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 269884559
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    64 |     6  (34)| 00:00:01 |
|*  1 |  HASH JOIN           |      |     1 |    64 |     6  (34)| 00:00:01 |
|   2 |   VIEW               |      |     3 |    78 |     3  (34)| 00:00:01 |
|   3 |    HASH GROUP BY     |      |     3 |    21 |     3  (34)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP  |    14 |    98 |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMP  |    14 |   532 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("INNER"."DEPTNO"="OUTER"."DEPTNO")
       filter("OUTER"."SAL">"INNER"."AVG_SAL")

關於謂詞推進和物化檢視的部分，稍後補充。