* Example of index being used in outer join


SQL> select * from test1, test2 where test1.c1=test2.c1(+);


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=23 Bytes=230)
   1    0   HASH JOIN (OUTER) (Cost=5 Card=23 Bytes=230)
   2    1     TABLE ACCESS (FULL) OF 'TEST1' (Cost=2 Card=23 Bytes=138
          )

   3    1     TABLE ACCESS (FULL) OF 'TEST2' (Cost=2 Card=9 Bytes=36)



SQL> create index test2_c1_idx on test2(c1);

Index created.

SQL> 
SQL> select /*+ index(test2 test2_c1_idx) */ * from test1, test2 where test1.c1=test2.c1(+);


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=48 Card=23 Bytes=230
          )

   1    0   NESTED LOOPS (OUTER) (Cost=48 Card=23 Bytes=230)
   2    1     TABLE ACCESS (FULL) OF 'TEST1' (Cost=2 Card=23 Bytes=138
          )

   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'TEST2' (Cost=2 Card=1
          Bytes=4)

   4    3       INDEX (RANGE SCAN) OF 'TEST2_C1_IDX' (NON-UNIQUE) (Cos
          t=1 Card=1)