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

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;

     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=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

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*/ e.* from scott.emp e,scott.emp t  2  where e.empno=t.mgr(+)  3    and t.mgr is null;

     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.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'