Re: cost of nested loop join

jernigam@xxxxxxxxxxx wrote:
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?


Count of blocks assumed to be accessed.
--
Daniel A. Morgan
University of Washington
damorgan@xxxxxxxxxxxxxxxx
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

Loading