Function-based indexes and histograms

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 Received on Mon Jan 14 2002 - 12:49:16 CST