# Nested Loop Join Costing

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

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
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
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.