Re: Intrepretation of 10053 trace

The index is a concatenated two column index, B*Tree. Nothing too special about it. I thought all you had to do to calculate a N/L cost was to do the following:

NL Cost = outer table cost + (outer table cardinality * inner table cost)

This makes complete sense to me sense, since the cost has to be assessed for each row in the outer table. The inner table cost here is the cost of doing a simple table access. That cost in my case is 211.

So from that logic I should have had gotten a cost of 2323 Instead of 45K I actually got.

Thanks for taking the time to repond.

Anyone????

spendius_at_muchomail.com (Spendius) wrote in message news:<aba30b75.0409230022.48a6eb71_at_posting.google.com>...
> pbhandari2050_at_yahoo.com (Pk) wrote...
> All I can tell you is that in the 2nd part the index path
> differs (first an index scan, then a join). I found nothing
> on Metalink (even internal) describing these RSC_CPU, RSC_IO,
> IX_SEL and TB_SEL parameters. What kind of index is OUT_ORD_HDR_AK_8 ?
>
> > SINGLE TABLE ACCESS PATH
> > TABLE: OUT_ORD_HDR ORIG CDN: 284368 ROUNDED CDN: 2393 CMPTD CDN: 2393
> > Access path: tsc Resc: 3083 Resp: 3083
> > Access path: index (scan)
> > Index: OUT_ORD_HDR_AK_8
> > TABLE: OUT_ORD_HDR
> > RSC_CPU: 0 RSC_IO: 211
> > IX_SEL: 8.4152e-03 TB_SEL: 8.4152e-03
> > BEST_CST: 211.00 PATH: 4 Degree: 1
>
> > Join order[1]: OUT_ORD_STAT [OUT_ORD_STAT] OUT_ORD_HDR
> > [OUT_ORD_HDR] CUSTOMER [CUSTOMER]
> > Now joining: OUT_ORD_HDR [OUT_ORD_HDR] *******
> > NL Join
> > Outer table: cost: 2 cdn: 11 rcz: 11 resp: 2
> > Inner table: OUT_ORD_HDR
> > Access path: tsc Resc: 3083
> > Join: Resc: 33915 Resp: 33915
> > Access path: index (join index)
> > Index: OUT_ORD_HDR_AK_8
> > TABLE: OUT_ORD_HDR
> > RSC_CPU: 0 RSC_IO: 4131
> > IX_SEL: 0.0000e+00 TB_SEL: 1.1111e-01
> > Join (ordered NL): resc: 45443 resp: 45443
> > Join cardinality: 2287 = outer (11) * inner (2393) *
> > sel (8.3333e-02) [flag=0]
> > Best NL cost: 33915 resp: 33915
Received on Thu Sep 23 2004 - 15:48:07 CDT