thiru@9.2.0:SQL>create table big as select * from all_objects;

Table created.

thiru@9.2.0:SQL>create index big_idx on big(object_name);

Index created.

thiru@9.2.0:SQL>create table small as select * from all_objects where rownum <= 100;

Table created.

thiru@9.2.0:SQL>create index small_idx on small(object_name);

Index created.

thiru@9.2.0:SQL>analyze table big compute statistics;

Table analyzed.

thiru@9.2.0:SQL>analyze table small compute statistics;

Table analyzed.

thiru@9.2.0:SQL>select table_name,num_rows,blocks from user_tables where table_name in ('BIG','SMALL');

TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
BIG                                 29803        862
SMALL                                 100          6

thiru@9.2.0:SQL>select big.object_id from big,small where big.object_name=small.object_name;

 OBJECT_ID
----------
     17286
      7559
     22542
...
     20472
     23683
     16167
     18321
     10335
     15677
     14095

196 rows selected.

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=136 Card=165 Bytes=9075)
   1    0   HASH JOIN (Cost=136 Card=165 Bytes=9075)
   2    1     TABLE ACCESS (FULL) OF 'SMALL' (Cost=3 Card=100 Bytes=2800)
   3    1     TABLE ACCESS (FULL) OF 'BIG' (Cost=132 Card=29803 Bytes=804681)

Statistics
----------------------------------------------------------
         12  recursive calls
          4  db block gets
        861  consistent gets
          0  physical reads
        828  redo size
       3195  bytes sent via SQL*Net to client
        642  bytes received via SQL*Net from client
         15  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        196  rows processed

-- above, note the timing and consistent gets.

-- Lets ask the CBO to use the index ,now

thiru@9.2.0:SQL>select /*+ INDEX(big) FULL(small) */ big.object_id from big,small where big.object_name=small.object_name;

 OBJECT_ID
----------
     17286
     17287
      7559
      7560
     22542

 
....
     14094
     14095

196 rows selected.

Elapsed: 00:00:00.09

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=303 Card=165 Bytes=9075)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'BIG' (Cost=3 Card=2 Bytes=54)
   2    1     NESTED LOOPS (Cost=303 Card=165 Bytes=9075)
   3    2       TABLE ACCESS (FULL) OF 'SMALL' (Cost=3 Card=100 Bytes=2800)
   4    2       INDEX (RANGE SCAN) OF 'BIG_IDX' (NON-UNIQUE) (Cost=2 Card=2)

Statistics
----------------------------------------------------------
         12  recursive calls
          4  db block gets
        412  consistent gets
          0  physical reads
        800  redo size
       2077  bytes sent via SQL*Net to client
        510  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        196  rows processed

-- the timing is little bit longer now,but consistent gets is lower.  But its pretty close, inspite of Index scan.This may not always be the case. Sometimes the consistent gets are drastically different .