Re: Intrepretation of 10053 trace

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

-- 
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