本帖最后由 ghsau 于 2011-10-11 23:39 編輯

請看下面三個執行計劃(Oracle10g)

1.??用IN

SQL> select ename from emp e where e.deptno in (select d.deptno from dept d where d.dname='SALES');

PLAN_TABLE_OUTPUT

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

Plan hash value: 351108634

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

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

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

|? ?0??| SELECT STATEMENT? ?? ?? ?? ?? ?? ?? ? |? ?? ?? ?? ???|? ???5? ?|? ?105 |? ???5? ?(0)| 00:00:01 |

|? ?1??|??NESTED LOOPS? ?? ?? ?? ?? ?? ?? ?? ?? ? |? ?? ?? ?? ???|? ???5? ?|? ?105 |? ???5? ?(0)| 00:00:01 |

|? ?2??|? ?TABLE ACCESS FULL? ?? ?? ?? ?? ?? ?? ?| EMP? ?? ? |? ? 14??|? ?126 |? ???3? ?(0)| 00:00:01 |

|*??3 |? ?TABLE ACCESS BY INDEX ROWID? ?| DEPT? ?? ?|? ???1? ?|? ? 12 |? ???1? ?(0)| 00:00:01 |

|*??4 |? ? INDEX UNIQUE SCAN? ?? ?? ?? ?? ?? ? | PK_DEPT |? ???1??|? ?? ?? ?|? ???0? ?(0)| 00:00:01 |

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

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

3 - filter("D"."DNAME"='SALES')

4 - access("E"."DEPTNO"="D"."DEPTNO")

2. 用EXISTS

SQL> select e.ename from emp e where exists (select 1 from dept d where e.deptno=d.deptno and d.dname='SALES');

PLAN_TABLE_OUTPUT

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

Plan hash value: 90266402

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

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

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

|? ?0 | SELECT STATEMENT? ?? ?? ?? ?? ?? ?? ?|? ?? ?? ?? ? |? ???5? ?|? ?105 |? ???5? ?(0)| 00:00:01 |

|? ?1 |??NESTED LOOPS SEMI? ?? ?? ?? ?? ?? ? |? ?? ?? ?? ???|? ???5??|? ?105 |? ???5? ?(0)| 00:00:01 |

|? ?2 |? ?TABLE ACCESS FULL? ?? ?? ?? ?? ?? ? | EMP? ?? ? |? ? 14??|? ?126 |? ???3? ?(0)| 00:00:01 |

|*??3 |? ?TABLE ACCESS BY INDEX ROWID| DEPT? ?? ?|? ???1? ?|? ? 12 |? ???1? ?(0)| 00:00:01 |

|*??4 |? ? INDEX UNIQUE SCAN? ?? ?? ?? ?? ? | PK_DEPT |? ???1? ?|? ?? ?? ?|? ???0? ?(0)| 00:00:01 |

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

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

3 - filter("D"."DNAME"='SALES')

4 - access("E"."DEPTNO"="D"."DEPTNO")

3.??用連接

SQL> select e.ename from emp e join dept d on e.deptno=d.deptno where d.dname='SALES';

PLAN_TABLE_OUTPUT

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

Plan hash value: 351108634

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

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

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

|? ?0 | SELECT STATEMENT? ?? ?? ?? ?? ?? ???|? ?? ?? ?? ?? ? |? ???5? ?|? ?105 |? ???5? ?(0)| 00:00:01 |

|? ?1 |??NESTED LOOPS? ?? ?? ?? ?? ?? ?? ?? ?? ?|? ?? ?? ?? ?? ?|? ???5? ?|? ?105 |? ???5? ?(0)| 00:00:01 |

|? ?2 |? ?TABLE ACCESS FULL? ?? ?? ?? ?? ?? ? | EMP? ?? ?? ?|? ? 14??|? ?126 |? ???3? ?(0)| 00:00:01 |

|*??3 |? ?TABLE ACCESS BY INDEX ROWID | DEPT? ?? ?|? ???1? ?|? ? 12? ?|? ???1? ?(0)| 00:00:01 |

|*??4 |? ? INDEX UNIQUE SCAN? ?? ?? ?? ?? ???| PK_DEPT |? ???1? ?|? ?? ?? ? |? ???0? ?(0)| 00:00:01 |

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

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

3 - filter("D"."DNAME"='SALES')

4 - access("E"."DEPTNO"="D"."DEPTNO")

按我來看，正常的執行效率應該是3>2>1，但是竟然產生了三個一模一樣的執行計劃，我原來看過的一個文檔是這樣說得：

其實我們區分in和exists主要是造成了驅動順序的改變(這是性能變化的關鍵)，如果是exists，那么以外層表為驅動表，先被訪問，如果是IN，那么先執行子查詢，所以我們會以驅動表的快速返回為目標，那么就會考慮到索引及結果集的關系了復制代碼這是其一，在執行計劃里沒有體現其二：我們通常是自己選擇優化器，還是用Oracle默認的優化器呢？

其三：Oracle10g有三種優化器，CHOOSE/COST/RULE，默認為CHOOSE，當表被analyze過時，選擇COST，否則選擇RULE，但是ORACLE10g默認分析表的，這里比較迷惑，求解