Re: Function-based indexes and histograms

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 14 Jan 2002 18:23:08 -0800
Message-ID: <a203qc0mrk@drn.newsguy.com>

In article <3ag64ugnvqaeheoi66gpmfie496mkae7lr_at_4ax.com>, Sybrand says...
>
>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

yes it will.

>- use the first *5* characters *only* to determine the histogram for
>varchar2 columns

correct

Here is an example showing:

different plans for different column values (implying histograms were used)
that user_tab_histograms has a SYS_NCnnnn$ column in it for the FBI

ops$tkyte_at_ORA815.US.ORACLE.COM> alter session set QUERY_REWRITE_ENABLED=TRUE; Session altered.

ops$tkyte_at_ORA815.US.ORACLE.COM> alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
Session altered.

ops$tkyte_at_ORA815.US.ORACLE.COM>
ops$tkyte_at_ORA815.US.ORACLE.COM> drop table t;

Table dropped.

ops$tkyte_at_ORA815.US.ORACLE.COM> 
ops$tkyte_at_ORA815.US.ORACLE.COM> 
ops$tkyte_at_ORA815.US.ORACLE.COM> create table t as

2 select decode( mod(rownum,1000), 1, 'A', 'B' ) data, rpad('*',128,'*') more_data
3 from all_objects;

Table created.

ops$tkyte_at_ORA815.US.ORACLE.COM>
ops$tkyte_at_ORA815.US.ORACLE.COM> create index t_idx1 on t(data);

Index created.

ops$tkyte_at_ORA815.US.ORACLE.COM> create index t_idx2 on t(substr(data,1,1));

Index created.

ops$tkyte_at_ORA815.US.ORACLE.COM>
ops$tkyte_at_ORA815.US.ORACLE.COM> analyze table t compute statistics   2 for table
  3 for all indexes
  4 for all indexed columns;

Table analyzed.

ops$tkyte_at_ORA815.US.ORACLE.COM> 
ops$tkyte_at_ORA815.US.ORACLE.COM> set autotrace traceonly explain
ops$tkyte_at_ORA815.US.ORACLE.COM> 
ops$tkyte_at_ORA815.US.ORACLE.COM> select * from t where data = 'A';

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=73 Bytes=5110) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=73 Bytes=5110) 2 1 INDEX (RANGE SCAN) OF 'T_IDX1' (NON-UNIQUE) (Cost=1 Card=73)

ops$tkyte_at_ORA815.US.ORACLE.COM> select * from t where data = 'B';

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=32 Card=10890 Bytes=762300) 1 0 TABLE ACCESS (FULL) OF 'T' (Cost=32 Card=10890 Bytes=762300)

ops$tkyte_at_ORA815.US.ORACLE.COM>
ops$tkyte_at_ORA815.US.ORACLE.COM> select * from t where substr(data,1,1) = 'A';

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=73 Bytes=5110) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=73 Bytes=5110) 2 1 INDEX (RANGE SCAN) OF 'T_IDX2' (NON-UNIQUE) (Cost=1 Card=73)

ops$tkyte_at_ORA815.US.ORACLE.COM> select * from t where substr(data,1,1) = 'B';

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=32 Card=10890 Bytes=762300) 1 0 TABLE ACCESS (FULL) OF 'T' (Cost=32 Card=10890 Bytes=762300)

ops$tkyte_at_ORA815.US.ORACLE.COM> 
ops$tkyte_at_ORA815.US.ORACLE.COM> set autotrace off
ops$tkyte_at_ORA815.US.ORACLE.COM> 
ops$tkyte_at_ORA815.US.ORACLE.COM> select table_name, column_name from

user_tab_histograms;

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
T                              DATA
T                              DATA
T                              SYS_NC00003$
T                              SYS_NC00003$

>
>Hth
>
>
>Sybrand Bakker, Senior Oracle DBA
>
>To reply remove -verwijderdit from my e-mail address

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Mon Jan 14 2002 - 20:23:08 CST