RBO和CBO下的NOT IN/NOT EXISTS与外关联

中国IT动力,最新最全的IT技术教程

最新100篇 | 推荐100篇 | 专题100篇 | 排行榜 | 搜索 | 在线API文档

当前位置：> 程序开发 > 数据库开发 > 数据库综合

RBO和CBO下的NOT IN/NOT EXISTS与外关联

作者:未知时间:2005-04-06 12:12 出处:Blog 责编:chinaitpower

摘要：暂无

SQL> analyze table scott.emp compute statistics for table for all columns;

表已分析。

已用时间: 00: 00: 06.06

SQL> select * from scott.emp e
2 where e.empno not in (select mgr from scott.emp);

未选定行

已用时间: 00: 00: 00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=32)
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 Bytes=32)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 Bytes=3)

SQL>
SQL> select * from scott.emp e
2 where not exists (select null from scott.emp s where s.mgr=e.empno);

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7844 TURNER     SALESMAN        7698 08-9月 -81       1500          0         30
      7521 WARD       SALESMAN        7698 22-2月 -81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-9月 -81       1250       1400         30
      7499 ALLEN      SALESMAN        7698 20-2月 -81       1600        300         30
      7934 MILLER     CLERK           7782 23-1月 -82       1300                    10
      7369 SMITH      CLERK           7902 17-12月-80        800                    20
      7876 ADAMS      CLERK           7788 23-5月 -87       1100                    20
      7900 JAMES      CLERK           7698 03-12月-81        950                    30

已选择8行。

已用时间: 00: 00: 00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=8 Bytes=280)
   1    0   HASH JOIN (ANTI) (Cost=5 Card=8 Bytes=280)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=448)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=13 Bytes=39)

SQL>
SQL> select e.* from scott.emp e,scott.emp t
2 where e.empno=t.mgr(+)
3 and t.mgr is null;

已选择8行。

已用时间: 00: 00: 00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=14 Bytes=490)
   1    0   FILTER
   2    1     HASH JOIN (OUTER)
   3    2       TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=448)
   4    2       TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=42)

SQL>
SQL> select /*+rule*/* from scott.emp e
2 where e.empno not in (select mgr from scott.emp);

未选定行

已用时间: 00: 00: 00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'EMP'
   3    1     TABLE ACCESS (FULL) OF 'EMP'

SQL>
SQL> select /*+rule*/* from scott.emp e
2 where not exists (select null from scott.emp s where s.mgr=e.empno);

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81       1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-9月 -81       1250       1400         30
      7844 TURNER     SALESMAN        7698 08-9月 -81       1500          0         30
      7876 ADAMS      CLERK           7788 23-5月 -87       1100                    20
      7900 JAMES      CLERK           7698 03-12月-81        950                    30
      7934 MILLER     CLERK           7782 23-1月 -82       1300                    10

已选择8行。

已用时间: 00: 00: 00.01

SQL>
SQL> select /*+rule*/ e.* from scott.emp e,scott.emp t
2 where e.empno=t.mgr(+)
3 and t.mgr is null;

已选择8行。

已用时间: 00: 00: 00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   FILTER
   2    1     MERGE JOIN (OUTER)
   3    2       SORT (JOIN)
   4    3         TABLE ACCESS (FULL) OF 'EMP'
   5    2       SORT (JOIN)
   6    5         TABLE ACCESS (FULL) OF 'EMP'

关闭本页