Re: cost of nested loop join

jernigam_at_kochind.com 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_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org