Re: Function-based indexes and histograms

Any chance we could talk you into any or all of the following?

1. Rewrite your current book for 9i
2. Write a DBA book for production DBAs
3. Write a book specifically for those migrating from SQL Server/Sybase/Informix/ DB2 UDB to Oracle

It is an education just to read your postings and try the examples.

Daniel Morgan

Thomas Kyte wrote:

> 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 Tue Jan 15 2002 - 09:05:16 CST