Re: CBO histograms & density

Stephan,

It will get very messy if I try to fit answers in, so I've collated a couple of comments.

The primary key - not surprised that density = 1/NDV, this is (probably) inevitable when the column is unique and not null.

The non-null ean requires slightly more justification - but since the number of different values is quite close to the number of rows, it is not a great surprise that the density = 1/NDV.

In fact, for any non-null column in the absence of histograms, density = 1/NDV; and if columns are nullable, then density = 1/NDC multiplied by "number of non-null rows / rowcount". And its only if the data is quite noticeably non-uniform that a histogram should make any difference.

So the primary key and EAN are behaving reasonably, it's just this one column that seems to be very odd.

Given that your table has 50,000 rows, 25,000 values, and no nulls for the one critical index, I would have thought that you would have to have lots of a single value that Oracle just happened to have sampled when building the histogram to produce such an odd result - but since you are doing a compute, this can't be the explanation.

Although the plan stays at a FTS whatever value you supply - you might put in an index() hint to make it use the index and find the cost of doing so - and then see if the cost of using the index varies with the input value.

Although the 50,000 rows may be fairly evenly spread over the range, you can get variations in cost if the gaps between end values are different. Since you have only 16 buckets, can you post the endpoint_number and endpoint_value columns (in order, preferably) for this column ?

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )



____USA__________November 7/9   (Detroit)

____USA__________November 19/21 (Dallas)

____England______November 12/14


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html