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