Why a double Nested Loop?

An interesting optimization has been done with Nested Loops in Oracle recently. In 11 in you may see plans like this:

SQL> set autotrace traceonly explain
SQL> select /*+ use_nl (d e) */ e.empno, e.sal, d.deptno, d.dname
  2  from emp e, dept d
  3  where e.deptno = d.deptno ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1688427947

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |    13 |   312 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |              |       |       |            |          |
|   2 |   NESTED LOOPS               |              |    13 |   312 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | DEPT         |     4 |    52 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | EMP_DEPT_IDX |     4 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP          |     3 |    33 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("E"."DEPTNO"="D"."DEPTNO")
       filter("E"."DEPTNO" IS NOT NULL)

Why the double Nested Loop?  We certainly expect to see the number of joins to be one less then the total number of tables, so with only 2 tables we'd expect to see just 1 join not 2.

What's happening here is that Oracle is "carrying" less in the inner loop.  It's joining the DEPT table just to index on EMP.  The results from this inner join is a row from the DEPT table and the ROWID from the EMP table, much less stuff to work with in the inner loop.  Joining the row of DEPT with just the index on EMP is certainly less then working with the entire row from both. 

The outer loop then really isn't a nested loop at all, but rather a "lookup" into the EMP table to find the row using the ROWID that it  retrieved from the inner loop. Now it has all the data, the row from DEPT and the row from EMP joined together.

Kinda cool eh?