cost of nested loop join

From: <jernigam_at_kochind.com>
Date: 20 Jun 2006 13:52:28 -0700
Message-ID: <1150836748.828596.153710@i40g2000cwc.googlegroups.com>

I am trying to determine how the CBO came up with the cost of a nested loop join.

Here is the query.
select
invd.invoice_id, invd.seq, invd.effective_date from invoice inv, invoice_d invd
where invd.invoice_id = inv.invoice_id
and inv.Issued_Date between trunc(sysdate, 'MM') and trunc(sysdate)

Here is my explain plan

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=102 Card=327 Bytes=9156)

   1 0 FILTER
   2 1 TABLE ACCESS (BY INDEX ROWID) OF 'INVOICE_D' (Cost=2 Card=5 Bytes=75)

   3    2       NESTED LOOPS (Cost=102 Card=327 Bytes=9156)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'INVOICE' (Cost=10
Card=61 Bytes=793)
   5    4           INDEX (RANGE SCAN) OF 'INVOICE_ISSUED' (NON-UNIQUE)
(Cost=3 Card=61)
   6    3         INDEX (RANGE SCAN) OF 'INVOICE_D_PK' (UNIQUE) (Cost=2

Card=5)

My understanding is that the cost should be equal to cost of outer table + ( cardinality of outer table + cost of inner table).

In this case I think that would be 10+(61*2)=132 Instead it equals 102. Is there something I am missing here? Received on Tue Jun 20 2006 - 15:52:28 CDT