分类:
2008-10-28 14:18:42
NESTED LOOP <Outer Loop> <Inner Loop>
SQL> select e.empno,e.ename,e.job,d.dname 2 from emp e,dept d 3 where e.deptno=d.deptno 4 and e.empno=7900; EMPNO ENAME JOB DNAME ---------- ---------- --------- -------------- 7900 JAMES CLERK SALES Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' 3 2 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE) 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' 5 4 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
Roby@XUE> select emp.ename,dept.dname 2 from emp,dept 3 where emp.deptno=dept.deptno(+); ENAME DNAME ---------- -------------- SMITH ALLEN WARD SALES JONES RESEARCH MARTIN SALES BLAKE SALES CLARK ACCOUNTING SCOTT RESEARCH KING ACCOUNTING TURNER SALES ADAMS RESEARCH JAMES SALES FORD RESEARCH MILLER ACCOUNTING 14 rows selected. Execution Plan ---------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 308 | 15 | 1 | NESTED LOOPS OUTER | | 14 | 308 | 15 | 2 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 |* 4 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0
[1]
SQL> select e.empno,e.ename,e.job,d.dname 2 from emp e,dept d 3 where e.deptno=d.deptno 4 and e.empno=7900; EMPNO ENAME JOB DNAME ---------- ---------- --------- -------------- 7900 JAMES CLERK SALES Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' 3 2 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE) 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' 5 4 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
Roby@XUE> select emp.ename,dept.dname 2 from emp,dept 3 where emp.deptno=dept.deptno(+); ENAME DNAME ---------- -------------- SMITH ALLEN WARD SALES JONES RESEARCH MARTIN SALES BLAKE SALES CLARK ACCOUNTING SCOTT RESEARCH KING ACCOUNTING TURNER SALES ADAMS RESEARCH JAMES SALES FORD RESEARCH MILLER ACCOUNTING 14 rows selected. Execution Plan ---------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 308 | 15 | 1 | NESTED LOOPS OUTER | | 14 | 308 | 15 | 2 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 |* 4 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0