oracle 自关联优化,浅析Oracle 10gR2中优化器对表关联的优化(转)

浅析Oracle 10gR2中优化器对表关联的优化(转)[@more@]在以前的一篇文章中，介绍过额外的关联对SQL的影响是很大的，所以在sql审核的工作中，对于表关联的关注是相当多的，如果冗余能够优化掉表关联，偶都尽量在表设计上做些冗余处理；在10gR2中，发现优化器可以优化掉一些不必要的关联……

9206:

SQL> set autot on

SQL> select count(*)

2 from dept d, emp e

3 where d.deptno = e.deptno;

COUNT(*)

----------

12

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT (AGGREGATE)

2 1 NESTED LOOPS

3 2 TABLE ACCESS (FULL) OF 'EMP'

4 2 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

10202

SQL> set autot on

SQL> select count(*)

2 from dept d, emp e

3 where d.deptno = e.deptno;

COUNT(*)

----------

12

ִѐ¼ƻ®

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

Plan hash value: 2083865914

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

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

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

| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

|* 2 | TABLE ACCESS FULL| EMP | 12 | 156 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("E"."DEPTNO" IS NOT NULL)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10617731/viewspace-963585/，如需转载，请注明出处，否则将追究法律责任。