So are you saying the cost can't be figured out by that formula?
DA Morgan wrote:
> 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
Received on Thu Jun 22 2006 - 08:19:14 CDT
