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

类别:数据库 点击:0 评论:0 推荐:

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'                                                     

本文地址:http://com.8s8s.com/it/it19188.htm