本帖最后由 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默认分析表的,这里比较迷惑,求解