Message-ID: <F001.0054BDD6.20030213072434@fatcity.com>
Date: Thu, 13 Feb 2003 07:24:34 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: Hemant K Chitale <hkchital@singnet.com.sg>
Subject: Fwd: Histograms on VARCHAR2 columns ?
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed
Content-Transfer-Encoding: 7bit


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 <ORACLE-L@fatcity.com>
>X-Sender: Hemant K Chitale <hkchital@singnet.com.sg>
>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: hkchital@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@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).