模板
教程
环境
性能
功能
管理
RBO和CBO下的NOT IN/NOT EXISTS与外关联
发表于：2007-07-02来源：作者：点击数：
标签：
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-
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@#
