Re: Intrepretation of 10053 trace

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<civ64g$ebg$1_at_sparta.btinternet.com>...
> The number of distinct keys in the index is 6 (DK)
>
> > INDEX NAME: OUT_ORD_HDR_AK_8 COL#: 4
> > TOTAL :: LVLS: 2 #LB: 680 #DK: 6 LB/K: 113 DB/K: 4017 CLUF:
> 24107
>
> The number of distinct values in the column is 9 -
> but there is an exact histogram in place.
>
> > Column: STATUS_CD Col#: 4 Table: OUT_ORD_HDR Alias: OUT_ORD_HDR
> > NDV: 9 NULLS: 0 DENS: 1.7583e-06
> > FREQUENCY HISTOGRAM: #BKT: 284368 #VAL: 9
>
>
> I'm guessing that the view includes some literal values,
> so on the Single Table Access path, Oracle can use
> the histogram to get the correct selectivity: (1 in 120
> rather than one in 6).
>
> > 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
>
> On the EQUALITY join, Oracle uses the table's
> selectivity (1 in 9 = .11111)and ignores the index DK.
>
> > 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
>

Thanks for pointing out the bit about Oracle throwing out the Index's selectivity for equality predicates. So, I recalculated the cost using the
table's selectivity (1/9) which came out to only 2756 which is still not as high as 4131. But when I used the selectivity of 1/6 then the cost came out to be
4133 (very close to 4131). Looks like this is exactly what's happening.

You were right about the index.. it is a single column index. So, the question is how to fix this problem? Is there any way to make oracle use the index selectivity short of manually modifying the table selectivity in the stats table?

Thanks.

Prakash

>
> --
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/seminar.html
> Optimising Oracle Seminar - schedule updated Sept 19th
>
>
>
>
>
> "Pk" <pbhandari2050_at_yahoo.com> wrote in message
> news:fa93fdc3.0409221506.4d28ffb2_at_posting.google.com...
> > Thanks Christian, here is the full trace file.
> >
> > Prakash
> >
> >
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> ++
> >
> >
> > QUERY
> > explain plan for SELECT * FROM kmk.OUT_CLIENT_LOAD_GRP_ASGN_VIEW
Received on Mon Sep 27 2004 - 12:49:30 CDT