The basic formula for calculating the costs of a Nested Loop Join is pretty straightforward and has been described and published several times.

In principle it is the cost of acquiring the driving row source plus the cost of acquiring the inner row source of the Nested Loop as many times as the driving row source dictates via the cardinality of the driving row source.

Cost (outer rowsource) + Cost (inner rowsource) * Card (outer rowsource)

Obviously there are cases where Oracle has introduced refinements to the above formula where this is no longer true. Here is one of these cases that is probably not uncommon.

Let’s start with a simple two table join that shows above formula in action. It represents a parent-child relationship where the parent table has 10,000 rows with a unique identifier, and a child table with 100 rows each that map to a single parent row, having 1,000,000 rows in total.

set echo 

on create table t 
as 
select 
          rownum as id 
        , rownum as attr1 
        , rpad('x', 100) as filler 
from 
        dual 
connect by 
        level <= 10000 
; 

exec dbms_stats.gather_table_stats(null, 't') 

create table t2 
as 
select 
           rownum as id 
         , mod(rownum, 10000) + 1 as fk 
         , mod(rownum, 20) + 1 as attr1 
         , rpad('x', 100) as filler 
from 
             dual 
connect by 
             level <= 1000000 
; 

exec dbms_stats.gather_table_stats(null, 't2') 

create index t2_idx on t2 (fk); 

explain plan for 
select /*+ use_nl(t t2) leading(t) index(t2) */ 
       * 
from 
       t 
     , t2 
where 
       t.attr1 <= 500 
and 
       t2.fk = t.id; 

set pagesize 0 linesize 200 tab off 

select * from table(dbms_xplan.display);

You can see the explanation plan this gives in 11.2 and read the full article on my blog.