浅析Oracle 10gR2中优化器对表关联的优化

2/9/2008来源:Oracle教程人气:8503

    在以前的一篇文章中,介绍过额外的关联对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)