Resending and hoping for some responses, even some real-world stories ... ?...
Date: Sun, 09 Feb 2003 18:38:35 -0800
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
X-Sender: Hemant K Chitale <[EMAIL PROTECTED]>
Subject: Histograms on VARCHAR2 columns ?
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 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).
