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'