當前位置：開發者網絡 >> 技術教程 >> 數據庫專欄 >> SQL Server >> 內容

精彩推薦 分類最新教程 分類熱點教程

RBO和CBO下的NOT IN/NOT EXISTS與外關聯 作者：未知 日期：2005-04-29 人氣： 投稿：(轉貼) 來源：未知 字體：大 中 小 收藏：加入瀏覽器收藏 以下正文： 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'                                                      相關文章：