SQL> Analyze Table Scott.emp Compute Statistics for Table for All Column;
The table has been analyzed.
Time: 00: 00: 06.06
SQL> Select * from scott.emp E 2 where e.empno not in (select mgr from scott.emp);
Unselected
Time: 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-September -81 1500 0 30 7521 Ward Salesman 7698 22-201 1250 500 30 7654 Martin Salesman 7698 28- September - 81 1250 1400 30 7499 Allen Salesman 7698 20-2 - 81 1600 300 30 7934 Miller Clerk 7782 23- Jan -82 1300 10 7369 Smith Clerk 7902 17-298 800 20 7876 Adams Clerk 7788 23-00-87 1100 20 7900 James Clerk 7698 03-December-81 950 30 Has selected 8 lines.
Time: 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-September -81 1500 0 30 7521 Ward Salesman 7698 22-201 1250 500 30 7654 Martin Salesman 7698 28- September - 81 1250 1400 30 7499 Allen Salesman 7698 20-2 - 81 1600 300 30 7934 Miller Clerk 7782 23- Jan -82 1300 10 7369 Smith Clerk 7902 17-298 800 20 7876 Adams Clerk 7788 23-00-87 1100 20 7900 James Clerk 7698 03-December-81 950 30 Has selected 8 lines.
Time: 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.em);
Unselected
Time: 00: 00: 00.00EXECUTION 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-2 month -80 800 20 7499 Allen Salesman 7698 20-2-81 1600 300 30 7521 Ward Salesman 7698 22-201-81 1250 500 30 7654 Martin Salesman 7698 28- September-81 1250 1400 30 7844 Turner Salesman 7698 08-September-81 1500 0 30 7876 Adams Clerk 7788 23-50 James Clerk 7698 03-293-81 950 30 7934 Miller Clerk 7782 23- Jan -82 1300 10
8 lines have been selected.
Time: 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-2 month -80 800 20 7499 Allen Salesman 7698 20-2-81 1600 300 30 7521 Ward Salesman 7698 22-201-81 1250 500 30 7654 Martin Salesman 7698 28- September-81 1250 1400 30 7844 Turner Salesman 7698 08-September-81 1500 0 30 7876 Adams Clerk 7788 23-50 James Clerk 7698 03-293-81 950 30 7934 Miller Clerk 7782 23- Jan -82 1300 10
8 lines have been selected.
Time: 00: 00: 00.00
