Re: Histograms on VARCHAR2 columns ?

Jonathan,

Thanks for the clarification. So, the leading 31 characters are used, not just 5 characters.

Regards
Hemant
At 04:43 AM 14-02-03 -0800, you wrote:

>The metalink note is out of date - newer versions
>of Oracle use the first 31 characters to build histograms.
>Of course, if you have a column of URLs for Google search
>results which all start with:
>
>
>http://groups.google.com/groups?hl=en&group=comp.databases.oracle.serv
>er
>
>then you may have to rethink your design a bit.
>
>
>Regards
>
>Jonathan Lewis
>http://www.jlcomp.demon.co.uk
>
>Coming soon one-day tutorials:
>Cost Based Optimisation
>Trouble-shooting and Tuning
>Indexing Strategies
>(see http://www.jlcomp.demon.co.uk/tutorial.html )
>
>____UK_______March 19th
>____USA_(FL)_May 2nd
>
>
>Next Seminar dates:
>(see http://www.jlcomp.demon.co.uk/seminar.html )
>
>____USA_(CA, TX)_August
>
>
>The Co-operative Oracle Users' FAQ
>http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>-----Original Message-----
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Date: 10 February 2003 03:15
>
>
> >
> >
> >I have just begun trying Column histograms and have
> >seen a better execution plan for a query
> >on LOTRUN.RECTYPE when
> > a) LOTRUN had about 5million records
> > b) RECTYPE='A' is only 1570 records
> > c) RECTYPE='H' is the other 5mn-1570 records
> >We wanted the RECTYPE Index to be used because all
> >of our queries are on RECTYPE='A'
> >The RBO would do an Index Range Scan which was quite good
> >However, the CBO under CHOOSE would do an Index Fast Full
> >Scan for both values.
> >
> >Once I collected column statistics with
> >ANALYZE TABLE LOTRUN COMPUTE STATISTICS FOR COLUMNS RECTYPE SIZE 10
>{I know, I don't need 10 buckets},
> >a query for RECTYPE='A' does an Index Range Scan with 10 consistent
>gets while the query for RECTYPE='H' does an
> >Index Fast Full Scan with 10060 consistent gets.
> >
> >Good !
> >So far so good, I should say.
> >
> >I look at Note 72539.1 on MetaLink and I find this paragraph :
> >
> > STORING CHARACTER VALUES IN HISTOGRAMS
> > --------------------------------------
> >
> > Character columns have some exceptional behaviour, in as much as we
>store
> > histogram data for the first 5 bytes of any string. Any predicates
>that
> > contain strings greater than 5 characters will not use histogram
>information
> > and the selectivity will be 1 / DISTINCT.
> >
> >
> >
> >Does this mean that a column with, say, 10 or 12 character
> >values but with the same character-string in the first
> >5 positions would not get meaningful histogram statistics ?
> >
> >
> >Hemant K Chitale
> >http://hkchital.tripod.com
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.net
> >--
> >Author: Hemant K Chitale
> > INET: hkchital_at_singnet.com.sg
> >
> >Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> >San Diego, California -- Mailing list and web hosting services
> >---------------------------------------------------------------------
> >To REMOVE yourself from this mailing list, send an E-Mail message
> >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> >the message BODY, include a line containing: UNSUB ORACLE-L
> >(or the name of mailing list you want to be removed from). You may
> >also send the HELP command for other information (like subscribing).
> >
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Jonathan Lewis
> INET: jonathan_at_jlcomp.demon.co.uk
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

Hemant K Chitale
My web site page is : http://hkchital.tripod.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hemant K Chitale
  INET: hkchital_at_singnet.com.sg

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).