SET SERVEROUTPUT OFF;
SET LINESIZE 2000;
ALTER SESSION SET STATISTICS_LEVEL = ALL;

SELECT /*+ USE_NL(E D) */
       *
FROM   EMP  E, DEPT D
WHERE  E.DEPTNO = D.DEPTNO
  AND  D.DEPTNO IN (10, 20, 30);   

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID   | EMP     |      1 |    900K|    22M|  9673   (1)| 00:01:57 |    999 |00:00:00.02 |     156 |
|   2 |   NESTED LOOPS                 |         |      1 |   2701K|   113M| 21467   (2)| 00:04:18 |   1003 |00:00:00.01 |      86 |
|   3 |    INLIST ITERATOR             |         |      1 |        |       |            |          |      3 |00:00:00.01 |       9 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      3 |    54 |     2   (0)| 00:00:01 |      3 |00:00:00.01 |       9 |
|*  5 |      INDEX RANGE SCAN          | DEPT_U1 |      3 |      3 |       |     1   (0)| 00:00:01 |      3 |00:00:00.01 |       6 |
|*  6 |    INDEX RANGE SCAN            | EMP_N1  |      3 |    906K|       |  4008   (2)| 00:00:49 |    999 |00:00:00.01 |      77 |
------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / E@SEL$1
   4 - SEL$1 / D@SEL$1
   5 - SEL$1 / D@SEL$1
   6 - SEL$1 / E@SEL$1

Outline Data                                                                                                                        
-------------                                                                                                                       
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      OPT_PARAM('_fast_full_scan_enabled' 'false')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
      INDEX(@"SEL$1" "E"@"SEL$1" ("EMP"."DEPTNO"))
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      USE_NL(@"SEL$1" "E"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access(("D"."DEPTNO"=10 OR "D"."DEPTNO"=20 OR "D"."DEPTNO"=30))
   6 - access("E"."DEPTNO"="D"."DEPTNO")
       filter(("E"."DEPTNO"=10 OR "E"."DEPTNO"=20 OR "E"."DEPTNO"=30))

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "E"."EMPNO"[NUMBER,22], "E"."EMPNO_VARCHAR"[VARCHAR2,40], "E"."JOB"[VARCHAR2,3], "E"."HIREDATE"[DATE,7],
       "E"."DEPTNO"[NUMBER,22]
   2 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,46], "D"."LOC"[VARCHAR2,45], "E".ROWID[ROWID,10],
       "E"."DEPTNO"[NUMBER,22]
   3 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,46], "D"."LOC"[VARCHAR2,45]
   4 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,46], "D"."LOC"[VARCHAR2,45]
   5 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
   6 - "E".ROWID[ROWID,10], "E"."DEPTNO"[NUMBER,22]