Re: Function-based indexes and histograms

On 14 Jan 2002 10:49:16 -0800, barnest_at_san.rr.com (Tom Barnes) wrote:

>Let's say I create a function-based index like this:
>
>CREATE INDEX my_index ON table1(SUBSTR(col1,1,10));
>
>and then I create a histogram for col1, like this:
>
>EXECUTE DBMS_STATS.GATHER_TABLE_STATS('tom','table1', METHOD_OPT =>
>'FOR COLUMNS SIZE 100 col1');
>
>Will Oracle somehow use the histogram when encountering a query with
>the SUBSTR(COL1,1,10) expression in the where-clause or should I just
>not bother creating the histogram and instead create a new column,
>col2, containing the SUBSTR(col1,1,10) data and build my index and
>histogram using col2?
>
>Oracle8i Enterprise Edition 8.1.6 on Solaris
>
>Thanks,
>
>Tom

Oracle will
- not use histograms for function based indexes - use the first *5* characters *only* to determine the histogram for varchar2 columns

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Jan 14 2002 - 14:37:37 CST