Oracle-L: Histograms on VARCHAR2 columns ?

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Sun, 09 Feb 2003 18:38:36 -0800
Message-ID: <F001.00548116.20030209183836@fatcity.com>

I have just begun trying Column histograms and have seen a better execution plan for a query on LOTRUN.RECTYPE when

LOTRUN had about 5million records
RECTYPE='A' is only 1570 records
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). Received on Sun Feb 09 2003 - 20:38:36 CST