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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > >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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
