在以前的一篇文章中，介绍过额外的关联对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)
转载于:https://www.cnblogs.com/myitworld/archive/2009/02/26/2214833.html
相关资源：JAVA上百实例源码以及开源项目