Re: Function-based indexes and histograms

I think the issue of how many characters of a varchar2() will be used in a histogram is highly version dependent. I have an example where a 10-character column is clearly subject to histogram analysis by the CBO. This is running under 8.1.7.0.0

I forget which version it appeared in (possibly 8.1.6), but the histgrm$ table gained an extra column which holds the 'exact end point value' for character columns. and I guess this is used when the normal 'first 6 bytes' that you see in column endpoint_value is inadequate.

An interesting point came out of my example,

I had an integer column with values from 1 to 80, and a character column which lpadded that with zeros to a length of 10 characters. Although I managed to show that the histogram was used, despite the fact that the first 6 bytes of the character column were identical, the effective quality of the histogram for the character data seemed to be much poorer than that for the numeric data. I only got two possible costs for queries against the character data, whereas there were several different costs against the numeric data.

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

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

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

Author of:
Practical Oracle 8i: Building Efficient Databases


Thomas Kyte wrote in message ...


>

>>- use the first *5* characters *only* to determine the histogram for

>>varchar2 columns

>

>correct

>